Database Reference
In-Depth Information
OUT parameters and records
Using a pre-existing type, table, or view for compound return types is a simple mech-
anism for returning more complex structures. However, there is often a need to define
the return type of the function with the function itself and not be dependent on other
objects. This is especially true when managing changes to a running application, so
over time two better ways to handle this have been added to PostgreSQL.
OUT parameters
Up until this point, all of the functions we have created have used parameters that are
defined as
IN
parameters. The
IN
parameters are meant to just pass information into
the function that can be used, but not returned. Parameters can also be defined as
OUT
or
INOUT
parameters if you want the function to return some information as well.
CREATE OR REPLACE FUNCTION positives(
INOUT a int,
INOUT b int,
INOUT c int)
AS $$
BEGIN
IF a < 0 THEN a = null; END IF;
IF b < 0 THEN b = null; END IF;
IF c < 0 THEN c = null; END IF;
END;
$$ LANGUAGE plpgsql;
When we run the previous function, notice that it only returns a single row of data.
hannu=# SELECT * FROM positives(-1, 1, 2);
-[ RECORD 1 ]
a |
b | 1
c | 2