A PL/pgSQL procedure in PostgreSQL

Buy Me a Coffee☕

A PL/pgSQL procedure:

• can have zero or more queries with BEGIN ... END clause getting zero or more values with zero or more parameters from the caller. *Be careful, BEGIN ... END clause is not transaction.

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

• can have DECLARE clause. *My post explains it.

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

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

• can have SELECT INTO statement.

• cannot have RETURN NEXT and RETURN QUERY statement.

• can have PERFORM statement.

• can have EXECUTE statement. *My post explains how to use EXECUTE statement in a function.

• can have RETURN statement without a value which is RETURN;.

• cannot have RETURN statement with a value e.g. RETURN 2; otherwise there is error.

• cannot have SELECT statement without INTO or INSERT, UPDATE or DELETE statement with RETURNING clause as a non-substatement otherwise there is the error.

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

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

• can control transaction with ROLLBACK and COMMIT.

• cannot control transaction with START TRANSACTION and BEGIN otherwise there is error.

• is similar to a MySQL procedure.

*The doc explains a PL/pgSQL procedure.
*My post explains a SQL procedure.
*My post explains a PL/pgSQL function.
*My post explains a SQL 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 result to the caller as shown below:

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

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

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

*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 cannot have VOID type in a SQL function otherwise there is error while an OUT parameter cannot have VOID type in a PL/pgSQL function:

• You must set AS <delimiter> clause and BEGIN ... END clause to a PL/pgSQL procedure otherwise there is error.

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

• You can use SELECT INTO statement to put a retrieved value into a variable.

• If a return value type doesn't match OUT or INOUT parameters, there is error.

• You must set LANGUAGE plpgsql to create a PL/pgSQL procedure otherwise there is the error.

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

Then, you can call my_proc(3, 10) 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 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 plpgsql
AS $$
BEGIN
  UPDATE test SET num = num + value;
  SELECT num INTO value 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)

And, you can use RETURN statement without a value which is RETURN; to exit my_proc() as shown below:

CREATE PROCEDURE my_proc(INOUT value INT)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN; -- Here
  UPDATE test SET num = num + value;
  SELECT num INTO value FROM test;
END;
$$;

Then, calling my_proc(3) returns 3 and nothing is added to num as shown below. *The INOUT parameter value is not changed from 3 to 5:

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

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

And, you can use PERFORM statement as shown below:

CREATE PROCEDURE my_proc(INOUT value INT)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE test SET num = num + value;
  PERFORM num FROM test; -- Here
END;
$$;

Then, calling my_proc(3) returns 3 and 2 is added to num as shown below. *The INOUT parameter value is not changed from 3 to 5:

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

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

And, you can control transaction with ROLLBACK and COMMIT as shown below. *Using START TRANSACTION and BEGIN gets error:

CREATE PROCEDURE my_proc(INOUT value INT)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE test SET num = num + value;
  ROLLBACK;
  SELECT num INTO value FROM test;
  COMMIT;
END;
$$;

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

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

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