Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Jun 18, 2015 at 10:31 AM, Sven Geggus <lists@xxxxxxxxxxxxxxxxxxxxx> wrote:
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.
 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux