Postgres Stored Procedures: how to return both multiple records with an out parameter?

Question

I have a function in Postgres 9.4 similar to this one:

CREATE OR REPLACE FUNCTION myF(
  INOUT _p1 character varying,
  IN _p2 integer,
  OUT _p3 boolean) 
RETURNS setof retVal AS 
$BODY$
  _p3 := '0';
  RETURN query SELECT 1 AS col1, 'test' as col2;
END;$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF
COST 100;

Having type retVal defined as well:

create type retVal as (col1 int, col2 character varying);

The body of the function is far more complex (and I need plpgsql for this purpose), and I have to invoke it from a Java program thru a

CallableStatement.executeQuery()

My questions are: 1) what should I put as RETURNS value in the function? 2) Is it correct to return the result via RETURN query syntax?

Thank you very much!

Pavel Stehule Pavel Stehule · Accepted Answer · 2016-08-24T10:46:33

It is not possible -function can returns scalar or record or set of scalars or set of records. Nothing more.

You can convert a set of records to array of records, and then you can return a record when one field is a array of records:

CREATE TYPE footype AS (a int, b int);

CREATE OR REPLACE FUNCTION public.foo(OUT x integer, OUT y footype[])
 RETURNS record
 LANGUAGE plpgsql
AS $function$
BEGIN
  x := 10;
  SELECT ARRAY(SELECT (i, i+1)::footype FROM generate_series(1,10) g(i)) INTO y;
  RETURN;
END;
$function$

postgres=# SELECT * FROM foo();
┌────┬──────────────────────────────────────────────────────────────────────────────────────┐
│ x  │                                          y                                           │
╞════╪══════════════════════════════════════════════════════════════════════════════════════╡
│ 10 │ {"(1,2)","(2,3)","(3,4)","(4,5)","(5,6)","(6,7)","(7,8)","(8,9)","(9,10)","(10,11)"} │
└────┴──────────────────────────────────────────────────────────────────────────────────────┘
(1 row)

Postgres Stored Procedures: how to return both multiple records with an out parameter?

1 Answers