A SQL procedure in PostgreSQL

A SQL procedure:

• can have zero or more queries with or without AS clause or BEGIN ATOMIC ... END clause getting zero or more values with zero or more parameters from the caller. *BEGIN ATOMIC END clause works only for LANGUAGE SQL according to the doc.

• can have IN, OUT, INOUT and VARIADIC parameters. *My answer explains a VARIADIC parameter.

• cannot have DECLARE clause otherwise there is error.

• cannot have RETURN NEXT and RETURN QUERY statement.

• can have type-only parameters. *My post explains it.

• can have the aliases of parameters e.g. $1, $2, etc. *My post explains it.

• cannot have SELECT INTO statement otherwise there is error.

• cannot have PERFORM statement otherwise there is error.

• cannot have EXECUTE statement otherwise there is error. *My post explains how to use EXECUTE statement in a function.

• cannot have RETURNS <type> clause otherwise there is error.

• can return a value with the last statement to the caller with SELECT statement or INSERT, UPDATE or DELETE statement with RETURNING clause using AS clause or BEGIN ATOMIC ... END clause.

• can return a value with the last statement to the caller with RETURN statement not using AS clause or using BEGIN ATOMIC ... END clause.

• cannot have BEGIN ... END clause which can have multiple queries otherwise there is error while a PL/pgSQL procedure can have it. *Be careful, BEGIN ... END clause is not transaction.

• is atomic by default running in a single transaction so if there is error, it is rollbacked automatically. *My answer explains it.

• cannot control transaction with START TRANSACTION, BEGIN, ROLLBACK, COMMIT, etc otherwise there is the error.

*The doc explains a SOL procedure.
*My post explains a PL/pgSQL procedure.
*My post explains a SQL function.
*My post explains a PL/pgSQL function.
*My post explains DO statement.

*You should use PL/pgSQL language because you can do much more things with it compared with SQL language.

For example, you create test table as shown below:

CREATE TABLE test (
  num INT
);

Then, you insert the row whose num is 2 into test table as shown below:

INSERT INTO test (num) VALUES (2);

Now, you can create my_proc() procedure which adds value to num and returns value to the caller as shown below:

CREATE PROCEDURE my_proc(IN value INT, OUT result INT)
LANGUAGE SQL
AS $$
UPDATE test SET num = num + value;
SELECT num FROM test;
$$;

Or, you can change the position of LANGUAGE SQL as shown below:

CREATE PROCEDURE my_proc(IN value INT, OUT result INT)
AS $$
UPDATE test SET num = num + value;
SELECT num FROM test;
$$
LANGUAGE SQL; -- Here

Or, you can use UPDATE statement with RETURNING num instead of SELECT num FROM test; as shown below:

CREATE PROCEDURE my_proc(IN value INT, OUT result INT)
LANGUAGE SQL
AS $$
UPDATE test SET num = num + value RETURNING num;
-- SELECT num FROM test;          -- ↑ Here ↑
$$;

*Memos:

• An IN parameter can get a value from the caller but cannot return a value to the caller and the parameter with and without IN is the same so my_proc(IN value INT) and my_proc(value INT) are the same:

• An OUT parameter can return a value to the caller but cannot get a value from the caller.

• An OUT parameter can have VOID type in a SQL function while an OUT parameter cannot in a PL/pgSQL function.

• The default value of an OUT parameter is NULL.

• You must set AS clause with delimiter to a SQL procedure otherwise there is error.

• You can also use other delimiter ' instead of $$ to create the body of a SQL procedure. *My answer explains it.

• Using BEGIN ... END statement in a SQL procedure gets error while a PL/pgSQL procedure doesn't get error:

• In a SQL procedure, the result of the last statement is the return value whose type must match OUT or INOUT parameters so in the example above, the result of SELECT num FROM test; is the return value whose type actually matches OUT result INT because num value is also INT so if the type doesn't match OUT result INT, there is error.

• You must set LANGUAGE SQL to create a SQL procedure otherwise there is the error.

• You can set LANGUAGE SQL in 2 positions as shown above.

Then, you can call my_proc(3) with CALL statement, then 5 is returned and 3 is added to num as shown below. *my_proc() without the 2nd argument or the 2nd argument whose type is not INT get the error:

postgres=# CALL my_proc(3, 10);
 result
--------
      5
(1 row)

postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

And, you can use VOID type to return nothing as shown below. *Type conversion is needed to VOID type and my answer explains how to convert type:

CREATE PROCEDURE my_proc(IN value INT, OUT result VOID)
LANGUAGE SQL                                    -- ↑ Here
AS $$
UPDATE test SET num = num + value RETURNING 'world'::VOID;
-- SELECT num FROM test;                     -- ↑ Here ↑
$$;

Then, calling my_proc(3, 'hello'::VOID) returns nothing which is VOID type and 3 is added to num as shown below. *Type conversion is needed to VOID type and you can use pg_typeof() to check the type 'Hello'::VOID which is VOID:

postgres=# CALL my_proc(3, 'hello'::VOID);
 result
--------

(1 row)

postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

And, you can use the INOUT parameter which is the combination of IN and OUT parameters to get a value from the caller and to return a value to the caller as shown below:

CREATE PROCEDURE my_proc(INOUT value INT)
LANGUAGE SQL
AS $$
UPDATE test SET num = num + value;
SELECT num FROM test;
$$;

Then, calling my_proc(3) returns 5 and 3 is added to num as shown below:

postgres=# CALL my_proc(3);
 value
-------
     5
(1 row)

postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

And, you can return a value with RETURN statement not using AS clause as shown below. *Using RETURN statement with AS clause gets the error:

CREATE PROCEDURE my_proc(INOUT value INT)
BEGIN ATOMIC
LANGUAGE SQL
  RETURN 2 + value;
END;

Or, you can unset LANGUAGE SQL when returning a value with RETURN statement not using AS clause as shown below but it is still a SQL procedure:

CREATE PROCEDURE my_proc(INOUT value INT)
-- LANGUAGE SQL
RETURN 2 + value;

Then, calling my_proc(3) adds returns 5 as shown below:

postgres=# CALL my_proc(3);
 value
-------
     5
(1 row)

And, you can use BEGIN ATOMIC ... END clause as shown below. *In BEGIN ATOMIC ... END clause, you can return a value with SELECT statement, RETURNING clause or RETURN statement and you can use both BEGIN ATOMIC and BEGIN ATOMIC;:

CREATE PROCEDURE my_proc(INOUT value INT)
LANGUAGE SQL
BEGIN ATOMIC
  UPDATE test SET num = num + value;
  SELECT num FROM test;
END;

Or:

CREATE PROCEDURE my_proc(INOUT value INT)
LANGUAGE SQL
BEGIN ATOMIC                        -- ↓ Here
  UPDATE test SET num = num + value RETURNING num;
END;

Or:

CREATE PROCEDURE my_proc(INOUT value INT)
LANGUAGE SQL
BEGIN ATOMIC
  UPDATE test SET num = num + value;
  RETURN (SELECT num FROM test);
END;

Or, you can unset LANGUAGE SQL when using BEGIN ATOMIC ... END clause as shown below but it is still a SQL procedure:

CREATE PROCEDURE my_proc(INOUT value INT)
-- LANGUAGE SQL
BEGIN ATOMIC
  UPDATE test SET num = num + value;
  SELECT num FROM test;
END;

Then, calling my_proc(3) returns 5 and 3 is added to num as shown below:

postgres=# CALL my_proc(3);
 value
-------
     5
(1 row)

postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)