A Comparison of Different Ways to Define and Return Outputs From PL/pgSQL Functions

hacking skills
Author

zenggyu

Published

2018-08-12

Abstract
Compare the differences among different ways of defining and returning PL/pgSQL function outputs.

Introduction

In PostgreSQL, the output of a function can be either: named or unnamed; of a base type or a record type; a scalar or a set. The above attributes of an output determines how a function is defined, how the output is returned from the function, and how the function should be called. With the many posibilities of combination, the underlying syntax can be very confusing to beginners. This post aimed to summarise the rules by making a comparison of different ways to define and return outputs in PL/pgSQL.

A comparison matrix

Comparisons between output attributes and consequences were conducted regarding: 1. data type (base type vs record type); 2. number of elements (scalar vs set); 3. name (named vs unnamed). For each combination of the above attributes, a PL/pgSQL function fxxx was defined and named according to :

Table 1: Comparison matrix
unnamed named
base
scalar f111 f112
set f121 f122
record
scalar f211 f212
set f221 f222

For example, a function that returns an unnamed scalar base type was named f111.

Based on the matrix, the following pair-wise comparisons were made:

  • unnamed vs named:
    • f111 vs f112
    • f121 vs f122
    • f211 vs f212
    • f221 vs f222
  • scalar vs set:
    • f111 vs f121
    • f112 vs f122
    • f211 vs f221
    • f212 vs f222
  • base vs record:
    • f111 vs f211
    • f112 vs f212
    • f121 vs f221
    • f122 vs f222

Code to compare

The following code shows the definition of each function and how they are called.

CREATE OR REPLACE FUNCTION f111 () RETURNS integer AS $body$
BEGIN
  RETURN 1;
END;
$body$ LANGUAGE plpgsql;

SELECT f111();
CREATE OR REPLACE FUNCTION f112 (OUT v_c1 integer) RETURNS integer AS $body$
BEGIN
  v_c1 := 1;
  RETURN;
END;
$body$ LANGUAGE plpgsql;

SELECT f112();
CREATE OR REPLACE FUNCTION f121 () RETURNS SETOF integer AS $body$
BEGIN
  RETURN next 1;
  RETURN next 2;
END;
$body$ LANGUAGE plpgsql;

SELECT f121();
SELECT * FROM f121();
CREATE OR REPLACE FUNCTION f122 (OUT v_c1 integer) RETURNS SETOF integer AS $body$
BEGIN
  v_c1 := 1;
  RETURN next;
  v_c1 := 2;
  RETURN next;
END;
$body$ LANGUAGE plpgsql;

SELECT f122();
SELECT * FROM f122();
CREATE OR REPLACE FUNCTION f211 () RETURNS record AS $body$
BEGIN
  RETURN row(1, 2);
END;
$body$ LANGUAGE plpgsql;

SELECT f211();
SELECT * FROM f211() AS t (c1 integer, c2 integer);
CREATE OR REPLACE FUNCTION f212 (OUT v_c1 integer, OUT v_c2 integer) RETURNS record AS $body$
BEGIN
  v_c1 := 1;
  v_c2 := 2;
  RETURN;
END;
$body$ LANGUAGE plpgsql;

SELECT f212();
SELECT * FROM f212();
SELECT (f212()).*;
CREATE OR REPLACE FUNCTION f221 () RETURNS SETOF record AS $body$
BEGIN
  RETURN next row(1, 2);
  RETURN next row(3, 4);
END;
$body$ LANGUAGE plpgsql;

SELECT * FROM f221() AS t (c1 integer, c2 integer);
CREATE OR REPLACE FUNCTION f222 (OUT v_c1 integer, OUT v_c2 integer) RETURNS SETOF record AS $body$
BEGIN
  v_c1 := 1;
  v_c2 := 2;
  RETURN next;
  v_c1 := 3;
  v_c2 := 4;
  RETURN next;
END;
$body$ LANGUAGE plpgsql;

SELECT (f222()).*;
SELECT * FROM f222();
SELECT f222();

Rules to take away

The followings are some take-away rules summarised from the comparisons.

  • Defining a function:
    • To define a function with named output parameters, use the OUT keyword in the function definition.
    • To define a function that returns a set, add the SETOF keyword in the function definition.
    • To define a function that returns a record, use the record type in the function definition. To define a function that returns a base type as output, use the corresponding type name (e.g., integer).
  • Returning an output:
    • If the output parameter(s) is/are named, then RETURN/RETURN NEXT must not be followed by any expression. However, if the output parameter(s) is/are unnamed, then RETURN/RETURN NEXT must be followed by an expression. In this context, an expression can be either a constant value (e.g., 1, ROW(1, 2)) or a declared variable, but not a SQL statement.
    • If the output is a scalar, use the RETURN keyword once to return the value. If the output is a set, use the RETURN NEXT keyword multiple times to return the values.
    • If the output is a record, then multiple values should be returned with each RETURN/RETURN NEXT statement. If the output is a base type, then only one value should be returned with each RETURN/RETURN NEXT statement.
  • Calling a function:
    • If the output is of a base type, always call the function in the SELECT expression list.
    • If the output is of a record type, always call the function in the FROM list. Additionally, if the output parameters are unnamed, add a column definition list after the function call (e.g., SELECT * FROM f211() AS t (c1 integer, c2 integer);).

1 As shown by the examples, sometimes there can be more than one way to call a function; to simplify the rules, I’ll stick with the ones that are most straightforward.

Additional notes

Although the output data type of f212()/f222() was specified as record, the actual data type was composite. This is because the output parameters were named and each had an explicit data type in the function definition. This is also the reason why, unlike f211/f221, f212()/f222() does not need a column definition list when it is called in the FROM list.

There are two other ways to define a function which returns an output of composite type. If the composite type already exists in the database, then simple specify the name of the type behind the RETURNS keyword. Another way is to use the syntax RETURNS TABLE (col_name data_type, ...), which is SQL standard-compliant and equivalent to using OUT parameter(s) with RETURNS SETOF record.

In addition to the RETURN NEXT statement, the RETURN QUERY statement can also be used to return a set. Here are some key differences:

  1. RETURN QUERY must be followed by one SQL statement, while RETURN NEXT can only accept nothing (when the output parameters are named) or a non-SQL expression (when the output parameters are unnamed).
  2. RETURN QUERY can return a scalar or a set at a time, while RETURN NEXT can only return a scalar at a time.

2 A constant, a variable, a function of constants and variables, etc.

RETURN QUERY EXECUTE is a variant of RETURN QUERY that can execute a dynamic SQL command and return the result as output.