A SQL function in PostgreSQL

Buy Me a Coffee☕

A SQL function:

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

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

• can return a value with the last statement to the caller with VALUES statement using AS clause or BEGIN ATOMIC ... END clause. *My answer explains it.

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

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

• 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 function.
*My post explains a PL/pgSQL function.
*My post explains a SOL procedure.
*My post explains a PL/pgSQL 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 $$
UPDATE test SET num = num + value;
SELECT num FROM test;
$$ LANGUAGE SQL;

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

CREATE FUNCTION my_func(value INT) RETURNS INT
LANGUAGE SQL -- Here
AS $$
UPDATE test SET num = num + value;
SELECT num FROM test;
$$;

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

CREATE FUNCTION my_func(value INT) RETURNS INT
AS $$
UPDATE test SET num = num + value RETURNING num;
$$ LANGUAGE SQL;                  -- ↑ Here ↑

*Memos:

• You must set RETURNS <type> clause or OUT or INOUT parameters which I explain later to a SQL 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 SQL function. *My answer explains it.

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

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

• You can set LANGUAGE SQL 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 to return nothing as shown below:

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

Then, calling my_func(3) returns nothing then 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 return a value with RETURN statement not using AS clause as shown below. *Using RETURN statement with AS clause gets the error:

CREATE FUNCTION my_func(value INT) RETURNS INT
LANGUAGE SQL
RETURN 2 + value;

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 function:

CREATE FUNCTION my_func(value INT) RETURNS INT
-- LANGUAGE SQL
RETURN 2 + value;

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

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

And, you can use BEGIN ATOMIC ... END clause in a SQL function 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 FUNCTION my_func(value INT) RETURNS INT
LANGUAGE SQL
BEGIN ATOMIC
  UPDATE test SET num = num + value;
  SELECT num FROM test; -- Here
END;

Or:

CREATE FUNCTION my_func(value INT) RETURNS INT
LANGUAGE SQL
BEGIN ATOMIC                   -- ↓ ↓ Here ↓ ↓
  UPDATE test SET num = num + value RETURNING num;
END;

Or:

CREATE FUNCTION my_func(value INT) RETURNS INT
LANGUAGE SQL
BEGIN ATOMIC
  UPDATE test SET num = num + value;
  RETURN (SELECT num FROM test); -- Here
END;

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

CREATE FUNCTION my_func(value INT) RETURNS INT
-- LANGUAGE SQL
BEGIN ATOMIC;
  UPDATE test SET num = num + value;
  SELECT num FROM test;
END;

Then, calling my_func(3) returns nothing, then 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)

In addition, you can use IN, OUT and INOUT parameters in a SQL 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
UPDATE test SET num = num + value;
SELECT num FROM test;
$$ LANGUAGE SQL;

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
UPDATE test SET num = num + 3;
SELECT num FROM test;
$$ LANGUAGE SQL;

Or:

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

*Memo:

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

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

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

• Passing a value to an OUT parameter gets the 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          -- ↑ Unset ↑
UPDATE test SET num = num + value;
SELECT num FROM test;
$$ LANGUAGE SQL;

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)