On Thu, Jun 18, 2015 at 10:31 AM, Sven Geggus <li...@fuchsschwanzdomain.de> wrote:
> David G. Johnston <david.g.johns...@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. > > 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.