Use of OUT and INOUT Parameters

PostgreSQL has supported what are called Out (output) parameters since version 8.1. We were surprised it has been that long since we always thought of it as a feature from 8.2+ until it recently came up for discussion on PostGIS newsgroup and we decided to investigate how long it has been supported.

What are OUT parameters? These are parameters you define as part of the function argument list that get returned back as part of the result. When you create functions, the arguments are defaulted to IN parameters when not explicitly specified (which means they are passed in and not returned) which is why you sometimes see PgAdmin do something like IN somevariable variabletype when you use the function wizard.

You can have INOUT parameters as well which are function inputs that both get passed in, can be modified by the function and also get returned.

As a side note - In 8.4, PostgreSQL was enhanced to allow dynamic sql RETURN QUERY using RETURN QUERY EXECUTE syntax for plpgsql queries and also allow set returning functions being called in the SELECT part for any pl language. In prior versions, this was only a feature of PL functions written in SQL. 8.3 introduced RETURN query which required a static sql statement, but did make things a bit easier.

One of the common use cases for using OUT parameters is to be able to return multiple outputs from a function without having to declare a PostgreSQL type as output of the function. In this article we shall cover all variants of this. We'll just focus on sql and plpgsql for this discussion, since we are not sure to what extent other pl languages (if at all) support IN OUT.

SQL and PLPGSQL examples of OUTPUT parameters - return single record

Below are some examples in plain SQL and their outputs.

--returning a single record using SQL
CREATE OR REPLACE FUNCTION fn_sqltestout(param_subject text, 
    OUT subject_scramble text, OUT subject_char text)
   AS
$$
    SELECT  substring($1, 1,CAST(random()*length($1) As integer)), substring($1, 1,1)
    $$
  LANGUAGE 'sql' VOLATILE;
  
SELECT  (fn_sqltestout('This is a test subject')).subject_scramble;

-- Output
 subject_scramble
------------------
 This is a test
 
 SELECT  (fn_sqltestout('This is a test subject')).*;
 --Output
   subject_scramble   | subject_char
----------------------+--------------
 This is a test subje | T
 
--Same function but written in plpgsql
--PLPGSQL example -- return one record
CREATE OR REPLACE FUNCTION fn_plpgsqltestout(param_subject text, 
    OUT subject_scramble text, OUT subject_char text)
   AS
$$
BEGIN
    subject_scramble := substring($1, 1,CAST(random()*length($1) As integer));
    subject_char := substring($1, 1,1);
END;
    $$
  LANGUAGE 'plpgsql' VOLATILE;

SQL OUTPUT parameters - return multiple records

--SQL returning multiple records
CREATE OR REPLACE FUNCTION fn_sqltestmulti(param_subject varchar, 
    OUT test_id integer, 
    OUT test_stuff text) 
    RETURNS SETOF record
   AS
$$
    SELECT test_id, test_stuff 
        FROM testtable where test_stuff LIKE $1;
$$
  LANGUAGE 'sql' VOLATILE;
  
--example
SELECT * FROM fn_sqltestmulti('%stuff%');
--example
--OUTPUT--
 test_id |     test_stuff
---------+--------------------
       1 | this is more stuff
       2 | this is new stuff
	   
	   
--PLPGSQL same using 8.3+ syntax
--OUT takes precendence which is why we prefix the table columns
CREATE OR REPLACE FUNCTION fn_plpgsqltestmulti(
    param_subject varchar, 
    OUT test_id integer, 
    OUT test_stuff text) 
    RETURNS SETOF record
   AS
$$
BEGIN
    RETURN QUERY SELECT t.test_id , t.test_stuff 
        FROM testtable As t 
    WHERE t.test_stuff LIKE param_subject;
END;
$$
  LANGUAGE 'plpgsql' VOLATILE;

INOUT parameters - return multiple records

--INOUT return multiple records SQL
CREATE OR REPLACE FUNCTION fn_sqltestmulti_inout(param_subject varchar, 
    INOUT test_id integer, 
    INOUT test_stuff text) 
    RETURNS SETOF record
   AS
$$
    SELECT $2 + test_id, $3 || test_stuff 
        FROM testtable 
        WHERE test_stuff LIKE $1;
$$
  LANGUAGE 'sql' VOLATILE;
  
--Example
  SELECT * FROM fn_sqltestmulti_inout('%stuff%',1, 'test');

 --OUTPUT
 test_id |    test_stuff
---------------+------------------------
       2 | testthis is more stuff
       3 | testthis is new stuff
			 
--INOUT same function in plpgsql 8.3+
--Note its a little odd to look at -- the INOUT param takes 
--precendence in naming which is why we prefix the table columns
CREATE OR REPLACE FUNCTION fn_plpgsqltestmulti_inout(
    param_subject varchar, 
    INOUT test_id integer, 
    INOUT test_stuff text) 
    RETURNS SETOF record
   AS
$$
BEGIN
    RETURN QUERY SELECT t.test_id + test_id , test_stuff || t.test_stuff  
        FROM testtable As t 
    WHERE t.test_stuff LIKE param_subject;
END;
$$
  LANGUAGE 'plpgsql' VOLATILE;