PL/PGSQL Function

PostgreSQL CREATE FUNCTION Statement:

Syntax:

CREATE FUNCTION function_name(p1 type, p2 type)
RETURNS type AS
BEGIN
-- logic
END;
LANGUAGE language_name;

Must follow:

First, specify the name of the function aftr the CREATE FUNCTION keywords.
Then, put a comma-separated list of parameters inside the parentheses following the function name.
Next, specify the return type of the function after the RETURNS keyword.
After that, place the code inside the BEGIN and END block. The function always ends with a semicolon (;) followed by the END keyword.
Finally, indicate the procedural language of the function e.g., plpgsql in case PL/pgSQL is

Example:

CREATE FUNCTION inc(val integer) RETURNS integer AS $$
BEGIN
RETURN val + 1;
END; $$
LANGUAGE PLPGSQL;

PL/pgSQL Function Parameters:

• IN parameters
• OUT parameters
• INOUT parameters
• VARIADIC parameters

PL/pgSQL IN parameters:

CREATE OR REPLACE FUNCTION get_sum(
a NUMERIC,
b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
RETURN a + b;
END; $$
LANGUAGE plpgsql;

The get_sum() function accepts two parameters: a, and b and returns a numeric. The data types of the two parameters are NUMERIC. By default, the parameter’s type of any parameter in PostgreSQL is IN parameter. You can pass the IN parameters to the function but you cannot get them back as a part of the result.

SELECT get_sum(10,20);

PL/pgSQL OUT parameters:

The OUT parameters are defined as part of the function arguments list and are returned back as a part of the result. PostgreSQL supported the OUT parameters since version 8.1

Input:

CREATE OR REPLACE FUNCTION hi_lo(
a NUMERIC,
b NUMERIC,
c NUMERIC,
OUT hi NUMERIC,
OUT lo NUMERIC)
AS $$
BEGIN
hi := GREATEST(a,b,c);
lo := LEAST(a,b,c);
END; $$
LANGUAGE plpgsql;

Output:

The hi_lo function accepts 5 parameters:
Three IN parameters: a, b, c.
Two OUT parameters: hi (high) and lo (low).
Inside the function, we get the greatest and least numbers of three IN parameters using GREATEST and LEAST built-in functions. Because we use the OUT parameters, we don’t need to have a RETURN tatement. The OUT parameters are useful in a function that needs to return multiple values without defining a custom type.
The following statement calls the hi_lo function:

SELECT hi_lo(10,20,30);

PL/pgSQL INOUT parameters:

The INOUT parameter is the combination IN and OUT parameters. It means that the caller can pass the value to the function. The function then changes the argument and passes the value back as a part of the result.
The following example shows you the square function that accepts a number and returns the square of that number.

Input:

CREATE OR REPLACE FUNCTION square(
INOUT a NUMERIC)
AS $$
BEGIN
a := a * a;
END; $$
LANGUAGE plpgsql;

Output:

SELECT square(4);

PL/pgSQL VARIADIC parameters:

A PostgreSQL function can accept a variable number of arguments with one condition that all arguments have the same data type. The arguments are passed to the function as an array. See the following example:

Input:

CREATE OR REPLACE FUNCTION sum_avg(
VARIADIC list NUMERIC[],
OUT total NUMERIC,
OUT average NUMERIC)
AS $$
BEGIN
SELECT INTO total SUM(list[i])
FROM generate_subscripts(list, 1) g(i);
SELECT INTO average AVG(list[i])
FROM generate_subscripts(list, 1) g(i);
END; $$
LANGUAGE plpgsql;

The sum_avg() function accepts a list of numbers, calculates the total and average, and returns both values.

SELECT * FROM sum_avg(10,20,30);