PL/pgSQL: Add static column to query result

Question

I have this function:

CREATE OR REPLACE FUNCTION func2(a integer[])
  RETURNS SETOF newset AS

$BODY$
declare 
    x int;
begin

  FOREACH x IN ARRAY $1 
  LOOP
    RETURN QUERY SELECT * FROM  func1(x); 
  END LOOP;

return;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE

func2 simply append all rows from all calls to func1. if first call to func1 gave 2 rows and second call gave 3 rows, func2 will return in total 5 rows (the rows themselves).

func1 returns a schema of 3 columns so currently func2 return same schema.

I want to change func2 so it will return 4 columns. the 3 from func1 and another column which contains the value of x.

for example: calling func2(ARRAY[500,200]) and assume func1(500) return 2 rows and func1(200) return 3 rows. I will get:

first  second third forth
a         b     c     500
d         e     f     500
g         h     i     200
j         k     l     200
m         n     o     200

I created a newset2 which is newset with another column of int for func2

CREATE OR REPLACE FUNCTION func2(a integer[])
  RETURNS SETOF newset2 AS

How do I add the desired column to the function?

score 2 · Accepted Answer · answered Jun 25 '15 at 11:33

2

You could just return the extra column:

RETURN QUERY SELECT *, x FROM  func1(x);

answered Jun 25 '15 at 11:33

Andomar

232,371
49
380
404

Can I order it by `x`? if i add `Order by` it doesn't work. it order specific call to `func1` instead of the whole total rows. – John Jun 25 '15 at 11:51
Your `func2` returns multiple times. In order to sort effectively, you could insert the various rowsets from `func1` into a temporary table and return that. – Andomar Jun 25 '15 at 13:09

score 0 · Answer 2 · edited May 23 '17 at 11:58

This can be substantially more efficient with a plain SELECT query using unnest() a LATERAL join. Applying any sort order is simpler, too.

SELECT f.*, x
FROM   unnest(ARRAY[500,200]) x, func1(x) f  -- implicit LATERAL join
ORDER  BY x;

That's all, including the additionally requested sort order.
It's a drop-in replacement for your SELECT * FROM func2(ARRAY[500,200]), no func2() needed.

You can still wrap this into a function, of course. I suggest a simple SQL function:

CREATE OR REPLACE FUNCTION func2(a integer[])
  RETURNS SETOF newset AS
$func$
 SELECT f.*, x
 FROM   unnest(ARRAY[500,200]) x, func1(x) f
 ORDER  BY x; 
$func$  LANGUAGE sql

The row type newset has to be pre-defined somehow.

PL/pgSQL: Add static column to query result

2 Answers2