A PL/pgSQL function in PostgreSQL

Buy Me a Coffee☕

A PL/pgSQL function:

• can have zero or more queries with BEGIN ... END statement 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.

• can have RETURN NEXT and RETURN QUERY statement.

  • My post explains RETURN NEXT statement.
  • My post explains RETURN QUERY statement.

• can have PERFORM statement.

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

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

• can return a value with RETURN statement, an OUT or INOUT parameters to the caller.

• 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 error.

• is similar to a MySQL function.

*The doc explains a PL/pgSQL function.
*My post explains a SQL function.
*My post explains a PL/pgSQL procedure.
*My post explains a SOL procedure.
*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_func() function which adds value to num and returns value to the caller as shown below:

CREATE FUNCTION my_func(value INT) RETURNS INT AS $$ BEGIN UPDATE test SET num = num + value; SELECT num INTO value FROM test; RETURN value; END; $$ LANGUAGE plpgsql; 

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

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

*Memos:

• You must set RETURNS <type> clause or OUT or INOUT parameters which I explain later otherwise there is error.

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

• RETURNS <type> clause can have VOID type to return nothing.

• You can also use other delimiter ' instead of $$ to create the body of a PL/pgSQL function. *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 RETURNS <type> clause or OUT or INOUT parameters, there is error.

• If you unset RETURN statement when RETURNS <type> clause is not VOID and an OUT or INOUT parameter is not used, there is the error.

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

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

Then, you can call my_func(3) with SELECT statement, then 5 is returned and 3 is added to num as shown below:

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

And, you can use VOID type with or without RETURN; to return nothing as shown below. *Using VOID type with RETURN <some-value>; in a PL/pgSQL function gets the error while a SQL function doesn't get the error:

CREATE FUNCTION my_func(value INT) RETURNS VOID AS $$ -- ↑ Here BEGIN UPDATE test SET num = num + value; SELECT num INTO value FROM test; RETURN; -- Here END; $$ LANGUAGE plpgsql; 

Or:

CREATE FUNCTION my_func(value INT) RETURNS VOID AS $$ -- ↑ Here BEGIN UPDATE test SET num = num + value; SELECT num INTO value FROM test; -- RETURN; -- Here END; $$ LANGUAGE plpgsql; 

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

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

And, you can use PERFORM statement as shown below:

CREATE FUNCTION my_func(value INT) RETURNS INT AS $$ BEGIN UPDATE test SET num = num + value; PERFORM num FROM test; -- Here RETURN 10; END; $$ LANGUAGE plpgsql; 

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

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

In addition, you can use IN, OUT and INOUT parameters in a PL/pgSQL function as shown below.

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

CREATE FUNCTION my_func(IN value INT) RETURNS INT AS $$ -- ↑↑ Here BEGIN UPDATE test SET num = num + value; SELECT num INTO value FROM test; RETURN value; END; $$ LANGUAGE plpgsql; 

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

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

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

CREATE FUNCTION my_func(OUT value INT) RETURNS INT AS $$ -- ↑ Here BEGIN UPDATE test SET num = num + 3; SELECT num INTO value FROM test; RETURN; END; $$ LANGUAGE plpgsql; 

Or:

CREATE FUNCTION my_func(OUT value INT) /* RETURNS INT */ AS $$ -- ↑ Here -- ↑ Unset ↑ BEGIN UPDATE test SET num = num + 3; SELECT num INTO value FROM test; -- RETURN; -- Unset END; $$ LANGUAGE plpgsql; 

*Memos:

• An OUT parameter and RETURNS <type> clause are the same except that an OUT parameter cannot have VOID type in a PL/pgSQL function while an OUT parameter can in a SQL function.

• When you set an OUT parameter, you can unset RETURNS <type> clause and RETURN statement.

• You can still set RETURNS <type> clause and RETURN statement with an OUT parameter but the types of an OUT parameter and RETURNS <type> clause must be the same otherwise there is the error and RETURN statement must be RETURN; to return nothing otherwise there is the error.

• Passing a value to an OUT parameter gets the error.

• Setting VOID type to an OUT parameter gets error.

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

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

An INOUT parameter is the combination of IN and OUT parameters to get a value from the caller and to return a value to the caller:

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

Or:

CREATE FUNCTION my_func(INOUT value INT) /* RETURNS INT */ AS $$ -- ↑ Here -- ↑ Unset ↑ BEGIN UPDATE test SET num = num + value; SELECT num INTO value FROM test; -- RETURN; -- Unset END; $$ LANGUAGE plpgsql; 

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

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