CALL and RETURN Statements

Statement CALL

This statement serves for activation of any function. In contrast to the standard, PostgreSQL doesn't support handover of the values by the link. This statement is analogical to the statement PERFORM in PL/pgSQL.

Syntax

  CALL '(' list of the values separated by comma ')'

Statement RETURN

Statement RETURN terminates the execution of a function. The result of the expression in the statement is also the value which is returned by the function. We don't have to produce the expression in the statement RETURN if it deals the function with OUT arguments or a void function. PL/pgPSM supports so called set returned functions.

Syntax

  RETURN [expression]

If function returns a table, we have to use so called table expression, when the expression in the statement RETURN is the SQL statement whose result is a table. Because the output table is directly handed over as the function output, statement SELECT without the clause INTO is used. Table expression can appear only as an argument of the statement RETURN.

CREATE OR REPLACE FUNCTION tab(p int)
RETURNS SETOF Foo AS
$$
  BEGIN
    -- specific calculation
    IF p < 0 THEN SET p = 0; END IF;
    IF p > 20 THEN SET p = 20; END IF;
    -- usage of the table expression
    RETURN TABLE(
      SELECT *
         FROM Foo
        WHERE Foo.a < tab.p;
      );  
  END;
$$ LANGUAGE plpgpsm;