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 while a SQL function cannot. *Be careful, BEGIN ... END; statement is not transaction.

• can have parameters with only types.

• can have the aliases of the parameters 1ドル, 2ドル, ...

• can have DECLARE clause while a SQL function cannot.

• can have IN, OUT and INOUT parameters.

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

• cannot control transaction with e.g. BEGIN; and COMMIT; because there is error.

• is run in a single transaction so if there is error, it is rollbacked.

*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;

*You can unset RETURN statement when RETURNS <type> is VOID type:

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)

And, you can use 1ドル(num1), 2ドル(num2), ... as the aliases of parameters as shown below. *My answer explains the aliases of paremeters and how to declare local variables in detail:

CREATE FUNCTION addition(num1 INTEGER, num2 INTEGER) RETURNS INTEGER AS $$
BEGIN
RETURN 1ドル + 2ドル;
END;
$$ LANGUAGE plpgsql;

*You can set parameters with only types as shown below:

CREATE FUNCTION addition(INTEGER, INTEGER) RETURNS INTEGER AS $$
BEGIN
RETURN 1ドル + 2ドル;
END;
$$ LANGUAGE plpgsql;

*You can use a local variable (value) with DECLARE clause as shown below:

CREATE FUNCTION addition(INTEGER, INTEGER) RETURNS INTEGER AS $$
DECLARE
 value INTEGER;
BEGIN
RETURN 1ドル + 2ドル;
END;
$$ LANGUAGE plpgsql;

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

postgres=# SELECT addition(2, 3);
 addition
----------
 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; and COMMIT; 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 a value from the caller but cannot return a value to 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 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 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 (INTEGER) and RETURNS <type> clause (VOID) are not the same, there is the error as shown below:

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

function result type must be integer because of OUT parameters

And, setting VOID type to an OUT parameter gets the error as shown below:

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

ERROR: PL/pgSQL functions cannot accept type void

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 a 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)