Re: PL/pgSQL: How to return two columns and multiple rows

On Thu, Jun 18, 2015 at 10:31 AM, Sven Geggus <lists(at)fuchsschwanzdomain(dot)de>
wrote:

> David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> > WITH exec_func AS ( SELECT myfunc(col1,col2) FROM mytable )
> > SELECT (exec_func.myfunc).* FROM exec_func;
> >
> > This relies on the fact that currently a CTE introduces an optimization
> > barrier.
>
> Hm, let me summarize. My function seems to work as expected and is only
> called once per row:
>
> Here is a working example:
>
> CREATE TYPE t_foobar AS (foo text, bar text);
> CREATE TABLE mytable (col1 text, col2 text);
> INSERT INTO mytable VALUES ('text1','value1');
> INSERT INTO mytable VALUES ('text2','value2');
>
> CREATE or REPLACE FUNCTION myfunc(foo text, bar text)
> returns SETOF t_foobar as $$
> BEGIN
> RAISE NOTICE 'called with parms foo,bar: % %',foo, bar;
> FOR i IN 1..4 LOOP
> RETURN NEXT (foo || ' ' || i::text, bar || ' ' || i::text);
> END LOOP;
> RETURN;
> END;
> $$ language 'plpgsql';
>
> mydb=> select myfunc(col1,col2) from mytable;
> NOTICE: called with parms foo,bar: text1 value1
> NOTICE: called with parms foo,bar: text2 value2
> myfunc
> ------------------------
> ("text1 1","value1 1")
> ("text1 2","value1 2")
> ("text1 3","value1 3")
> ("text1 4","value1 4")
> ("text2 1","value2 1")
> ("text2 2","value2 2")
> ("text2 3","value2 3")
> ("text2 4","value2 4")
> (8 rows)
>
> Using your suggestion the desired two columns are generated, but I consider
> this a little bit ugly:
>
> SELECT (exec_func.myfunc).* FROM exec_func;
> mydb=> WITH exec_func AS ( select myfunc(col1,col2) from mytable )
> SELECT (exec_func.myfunc).* FROM exec_func;
> HINWEIS: called with parms foo,bar: text1 value1
> HINWEIS: called with parms foo,bar: text2 value2
> foo | bar
> ---------+----------
> text1 1 | value1 1
> text1 2 | value1 2
> text1 3 | value1 3
> text1 4 | value1 4
> text2 1 | value2 1
> text2 2 | value2 2
> text2 3 | value2 3
> text2 4 | value2 4
> (8 rows)
>
> I would rather have a functiuon which already returns the desired two
> columns.
>
>
​the function is not the problem - its how you choose to incorporate it
into the query.

Assuming you are on 9.3+ what you want to use is LATERAL

Or you could move the CTE to a sub-query with an OFFSET 0 specification
(again, to prevent optimization).

David J.
​