Summary: in this tutorial, we will introduce you to various kinds of PL/pgSQL function parameters: IN, OUT , INOUT and VARIADIC.
PL/pgSQL IN parameters
Let’s start with an example of creating new function called get_sum() as follows:
1 2 3 4 5 6 7 8 9 | 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 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 result.
1 | SELECT get_sum(10,20); |
PL/pgSQL OUT parameters
The OUT parameters is a part of the function arguments list and you can get the result back as the part of the result. To define OUT parameters, you use OUT keyword. See the following example:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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; |
The hi_lo function accepts 5 parameters:
- Three
INparameters:a,b,c. - Two
OUTparameters:hi(high) andlo(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 statement. 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:
1 | SELECT hi_lo(10,20,30); |
The output of the function is a record, which is a custom type. To make the output separated as columns, you use the following syntax:
1 | SELECT * FROM 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.
1 2 3 4 5 6 7 | CREATE OR REPLACE FUNCTION square( INOUT a NUMERIC) AS $$ BEGIN a := a * a; END; $$ LANGUAGE plpgsql; |
1 | SELECT square(4); |
PL/pgSQL VARIADIC parameters
A PostgreSQL function can accept a variable numbers 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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.
1 | SELECT * FROM sum_avg(10,20,30); |
In this tutorial, you have learned four type of function parameters including IN, OUT, INOUT and VARIADIC.