Return to Revisions

A PL/pgSQL function:

• can run multiple SQL statements with BEGIN ... END; statement getting multiple values with multiple parameters from the caller. *Be careful, BEGIN ... END; statement is not transaction.

• can have IN, OUT and INOUT parameters.

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

• cannot use transaction with e.g. BEGIN;, COMMIT;, etc because there is error.

*The doc explains a PL/pgSQL function in detail and my answer explains a SQL function.

For example, you create test table as shown below:

CREATE TABLE test (
 num INTEGER
);

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

INSERT INTO test (num) VALUES (2);

Now, you can create addition(value INTEGER) function which adds value to num and returns value to the caller as shown below. *You must set RETURNS <type> clause or OUT or INOUT parameters which I explain later and AS <delimiter> clause and BEGIN ... END; statement to a PL/pgSQL function otherwise there is error and RETURNS <type> clause can have VOID type to return nothing and you can also use other delimiter ' instead of $$ to create the body of a PL/pgSQL function and you can use SELECT ... INTO statement to put a retrieved value into a variable and if a return value type doesn't match RETURNS <type> clause or OUT or INOUT parameters, there is error and lastly, you must set plpgsql to LANGUAGE to create a PL/pgSQL function:

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER AS $$
BEGIN
UPDATE test set num = num + value;
SELECT num INTO value FROM test;
RETURN value;
END;
$$ LANGUAGE plpgsql;

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

postgres=# SELECT addition(3);
 addition
----------
 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:

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

Or:

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

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

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

Be careful, using VOID type with RETURN value; in a PL/pgSQL function gets the error as shown below because their types VOID and INTEGER are not the same while a SQL function doesn't get error:

CREATE FUNCTION addition(value INTEGER) RETURNS VOID AS $$
BEGIN -- ↑ Here
UPDATE test set num = num + value;
SELECT num INTO value FROM test;
RETURN value; -- Error
END;
$$ LANGUAGE plpgsql;

ERROR: RETURN cannot have a parameter in function returning void
LINE 5: RETURN value; -- Here

And, not using BEGIN ... END; in a PL/pgSQL function gets the error as shown below while a SQL function doesn't get error:

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER AS $$
-- BEGIN -- Error
UPDATE test set num = num + value;
SELECT num INTO value FROM test;
RETURN value;
-- END; -- Error
$$ LANGUAGE plpgsql;

ERROR: syntax error at or near "UPDATE"
LINE 2: UPDATE test set num = num + value;

And, using transaction with e.g. BEGIN;, COMMIT;, etc in a PL/pgSQL function gets the error as shown below:

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER AS $$
BEGIN
BEGIN;
UPDATE test set num = num + value;
SELECT num INTO value FROM test;
RETURN value;
COMMIT;
END;
$$ LANGUAGE plpgsql;

ERROR: syntax error at or near ";"
LINE 3: BEGIN;

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

An IN parameter can get the value from the caller and actually, with and without IN is the same so addition(IN value INTEGER) and addition(value INTEGER) are the same:

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

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

postgres=# SELECT addition(3);
 addition
----------
 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 and actually, an OUT parameter and RETURNS <type> clause are the same so an OUT parameter can also have VOID type while an IN parameter cannot. *When you set an OUT parameter, you can unset RETURNS <type> clause and RETURN statement and if you set RETURN statement with an OUT parameter, you must set RETURN; to return nothing otherwise there is error:

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

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

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

But, calling addition(3) gets the error as shown below because an OUT parameter cannot get a value from the caller as I said above:

postgres=# SELECT addition(3);
ERROR: function addition(integer) does not exist
LINE 1: SELECT addition(3);
 ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

And, 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 and RETURN statement must be RETURN; to return nothing as shown below otherwise there is error:

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

So, if the types of an OUT parameter (VOID) and RETURNS <type> clause (INTEGER) are not the same, there is the error as shown below:

CREATE FUNCTION addition(OUT value VOID) RETURNS INTEGER AS $$
BEGIN -- ↑ Here -- ↑ Here
UPDATE test set num = num + 3;
SELECT num INTO value FROM test;
-- RETURN; -- Unset
END;
$$ LANGUAGE plpgsql;

ERROR: function result type must be void because of OUT parameters

And, setting RETURN value; instead of RETURN; with an OUT parameter gets the error as shown below:

CREATE FUNCTION addition(OUT value INTEGER) AS $$
BEGIN -- ↑ Here
UPDATE test set num = num + 3;
SELECT num INTO value FROM test;
RETURN value;
END; -- ↑ Here
$$ LANGUAGE plpgsql;

ERROR: RETURN cannot have a parameter in function with OUT parameters
LINE 5: RETURN value;

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

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

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

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