PostgreSQL - CREATE PROCEDURE

In PostgreSQL, a procedure is similar to a function in that it can execute SQL statements and perform operations in the database. However, there are key differences:

A procedure does not return a value like a function does. Instead, it performs actions and can have OUT parameters.
Procedures can have transaction control statements like COMMIT and ROLLBACK, while functions cannot.

Starting from PostgreSQL 11, you can use the CREATE PROCEDURE statement to define a procedure. Additionally, to call a procedure, you use the CALL statement.

Syntax:

CREATE PROCEDURE procedure_name (parameter_list)
LANGUAGE plpgsql
AS $$
-- procedure body
$$;

procedure_name: The name of the procedure.
parameter_list: List of parameters in the format param_name param_type [IN | OUT | INOUT]. By default, parameters are IN.

Example:

Let's create a simple procedure that inserts a new record into a hypothetical logs table and then commits the transaction:

CREATE PROCEDURE insert_log(p_message TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO logs(message) VALUES (p_message);
    COMMIT;
END;
$$;

To call the above procedure:

CALL insert_log('This is a test log message.');

Using OUT Parameters:

You can use OUT parameters to return values from procedures:

CREATE PROCEDURE get_total_logs(OUT total INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*) INTO total FROM logs;
END;
$$;

When you call this procedure, it will provide the total number of logs:

CALL get_total_logs();

Note:

In many cases, using a function might be more appropriate than a procedure, especially if you need to compute and return values based on the data.
Procedures, given their ability to manage transactions within, are useful for scenarios where multiple SQL statements need to be executed together, and there's a possibility of committing or rolling back the changes based on some logic.
Like functions, ensure your procedures are thoroughly tested to avoid unwanted side effects, especially since procedures can contain transaction control statements that can modify the database state.

Creating stored procedures in PostgreSQL:
- Description: Creates a reusable set of SQL statements as a stored procedure.
- Code:
```
CREATE OR REPLACE PROCEDURE your_procedure()
LANGUAGE plpgsql AS $$
-- Procedure logic here
$$
```
Input and output parameters in CREATE PROCEDURE in PostgreSQL:
- Description: Specifies input and output parameters for the stored procedure.
- Code:
```
CREATE OR REPLACE PROCEDURE your_procedure(param1 INTEGER, OUT result INTEGER)
LANGUAGE plpgsql AS $$
-- Procedure logic here
$$
```

Transaction control within procedures in PostgreSQL:

Description: Manages transactions within the stored procedure.

Code:

CREATE OR REPLACE PROCEDURE your_procedure()
LANGUAGE plpgsql AS $$
BEGIN
    -- Transaction logic
    COMMIT;
END;
$$

Exception handling in stored procedures in PostgreSQL:

Description: Catches and handles exceptions within the stored procedure.

Code:

CREATE OR REPLACE PROCEDURE your_procedure()
LANGUAGE plpgsql AS $$
BEGIN
    -- Procedure logic
    EXCEPTION
        WHEN others THEN
            -- Exception handling logic
            ROLLBACK;
END;
$$;

Managing security and permissions for procedures in PostgreSQL:
- Description: Grants appropriate permissions to users or roles for the stored procedure.
- Code:
```
GRANT EXECUTE ON PROCEDURE your_procedure() TO your_user;
```
Dropping procedures with DROP PROCEDURE in PostgreSQL:
- Description: Removes a stored procedure from the database.
- Code:
```
DROP PROCEDURE your_procedure();
```

Procedure overloading and polymorphism in PostgreSQL:

Description: Creates multiple procedures with the same name but different parameter lists.

Code:

CREATE OR REPLACE PROCEDURE your_procedure(param1 INTEGER)
LANGUAGE plpgsql AS $$ ... $$;

CREATE OR REPLACE PROCEDURE your_procedure(param1 TEXT)
LANGUAGE plpgsql AS $$ ... $$;

Calling procedures from other procedures in PostgreSQL:

Description: One procedure can call another procedure.

Code:

CREATE OR REPLACE PROCEDURE procedure1()
LANGUAGE plpgsql AS $$
BEGIN
    -- Procedure1 logic
    CALL procedure2();
END;
$$;

CREATE OR REPLACE PROCEDURE procedure2()
LANGUAGE plpgsql AS $$ ... $$;