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 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 clause or OUT or INOUT parameters which I explain later and AS <delimiter> clause and BEGIN ... END; statement and RETURN statement to a PL/pgSQL function otherwise there is error 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 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][10] 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 without RETURN value; or 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 value; -- Unset
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; -- Here
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 get the error as shown below while a PL/pgSQL function doesn't get error:

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER AS $$
-- BEGIN -- Here
UPDATE test set num = num + value;
SELECT num INTO value FROM test;
RETURN value;
-- END; -- Here
$$ 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 SQL 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 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 clause are the same so an OUT parameter can also have VOID type while an IN parameter cannot. *You can unset RETURNS clause and RETURN statement when you set an OUT parameter:

CREATE FUNCTION addition(OUT value INTEGER) AS $$
BEGIN -- ↑ Here
UPDATE test set num = num + 3;
SELECT num INTO value FROM test;
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) get 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 clause and RETURN statement with an OUT parameter but the types of an OUT parameter and RETURNS 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 and RETURNS clause 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;
END;
$$ LANGUAGE plpgsql;

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

And, setting RETURNS value; 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 can get the value from the caller and return a value to the caller so an INOUT parameter has the characteristics of IN and OUT parameters which I explain above:

CREATE FUNCTION addition(INOUT value INTEGER) AS $$
BEGIN
UPDATE test set num = num + value;
SELECT num INTO value FROM test;
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)