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