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)