Re: PL/pgSQL: How to return two columns and multiple rows - Mailing list pgsql-general

David G. Johnston <david.g.johnston@gmail.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.