Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
On Thu, Jun 18, 2015, 14:38 Sven Geggus li...@fuchsschwanzdomain.de wrote: Hello, I supose this is simple, but I did not find a solution in the documentation. Because you already are returning 2 columns. I would like to be able to do something like this: select myfunc('foo','bar'); or select myfunc(foo, bar) from foobartable; or even select myfunc(foo, bar), 'baz' as baz from foobartable; Which should return something like this: foo | bar --+-- foo1 | bar1 foo2 | bar2 foo3 | bar3 foo4 | bar4 (4 rows) So the output should be at least two columns and (usually) more than one row. What I currently have is the following, which is mostly it. Unfortunately it gives me only one column (I really need two) and I would have to create a custom type: CREATE TYPE t_foobar AS (foo text, bar text); CREATE or REPLACE FUNCTION myfunc(foo text, bar text) returns SETOF t_foobar as $$ BEGIN FOR i IN 1..4 LOOP RETURN NEXT (foo || i::text, bar || i::text); END LOOP; RETURN; END; $$ language 'plpgsql'; mydb= select myfunc('foo','bar'); myfunc - (foo1,bar1) (foo2,bar2) (foo3,bar3) (foo4,bar4) (4 rows) Select (myfunc('foo','bar')).*; Or Select * from myfunc('foo','bar'); Regards Sven -- Exploits and holes are a now a necessary protection against large corporate interests. (Alan Cox) /me is giggls@ircnet, http://sven.gegg.us/ on the Web -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
Pavel Stehule pavel.steh...@gmail.com wrote: CREATE OR REPLACE FUNCTION public.fx(i integer, OUT a text, OUT b text) RETURNS SETOF record LANGUAGE plpgsql AS $function$ BEGIN RETURN QUERY SELECT 'foo'||id, 'bar'||id FROM generate_series(1,i) g(id); RETURN; END; $function$ I'm afraid I will almost certainly be unable to use RETURN QUERY. I have just broken this down to the posted code to make it easier to understand. In my real world code a loop will need to iterate over features of a postgis geometry returning a couple of rows containing a string and a calculated geometry as a result. Regards Sven -- Thinking of using NT for your critical apps? Isn't there enough suffering in the world? (Advertisement of Sun Microsystems in Wall Street Journal) /me is giggls@ircnet, http://sven.gegg.us/ on the Web -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
Hi CREATE OR REPLACE FUNCTION public.fx(i integer, OUT a text, OUT b text) RETURNS SETOF record LANGUAGE plpgsql AS $function$ BEGIN RETURN QUERY SELECT 'foo'||id, 'bar'||id FROM generate_series(1,i) g(id); RETURN; END; $function$ postgres=# SELECT a,b FROM fx(4); ┌──┬──┐ │ a │ b │ ╞══╪══╡ │ foo1 │ bar1 │ │ foo2 │ bar2 │ │ foo3 │ bar3 │ │ foo4 │ bar4 │ └──┴──┘ (4 rows) Regards Pavel 2015-06-18 14:36 GMT+02:00 Sven Geggus li...@fuchsschwanzdomain.de: Hello, I supose this is simple, but I did not find a solution in the documentation. I would like to be able to do something like this: select myfunc('foo','bar'); or select myfunc(foo, bar) from foobartable; or even select myfunc(foo, bar), 'baz' as baz from foobartable; Which should return something like this: foo | bar --+-- foo1 | bar1 foo2 | bar2 foo3 | bar3 foo4 | bar4 (4 rows) So the output should be at least two columns and (usually) more than one row. What I currently have is the following, which is mostly it. Unfortunately it gives me only one column (I really need two) and I would have to create a custom type: CREATE TYPE t_foobar AS (foo text, bar text); CREATE or REPLACE FUNCTION myfunc(foo text, bar text) returns SETOF t_foobar as $$ BEGIN FOR i IN 1..4 LOOP RETURN NEXT (foo || i::text, bar || i::text); END LOOP; RETURN; END; $$ language 'plpgsql'; mydb= select myfunc('foo','bar'); myfunc - (foo1,bar1) (foo2,bar2) (foo3,bar3) (foo4,bar4) (4 rows) Regards Sven -- Exploits and holes are a now a necessary protection against large corporate interests. (Alan Cox) /me is giggls@ircnet, http://sven.gegg.us/ on the Web -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
On Thursday, June 18, 2015, Sven Geggus li...@fuchsschwanzdomain.de wrote: Hello, I supose this is simple, but I did not find a solution in the documentation. I would like to be able to do something like this: select myfunc('foo','bar'); or select myfunc(foo, bar) from foobartable; or even select myfunc(foo, bar), 'baz' as baz from foobartable; Which should return something like this: foo | bar --+-- foo1 | bar1 foo2 | bar2 foo3 | bar3 foo4 | bar4 (4 rows) So the output should be at least two columns and (usually) more than one row. What I currently have is the following, which is mostly it. Unfortunately it gives me only one column (I really need two) and I would have to create a custom type: CREATE TYPE t_foobar AS (foo text, bar text); CREATE or REPLACE FUNCTION myfunc(foo text, bar text) returns SETOF t_foobar as $$ BEGIN FOR i IN 1..4 LOOP RETURN NEXT (foo || i::text, bar || i::text); END LOOP; RETURN; END; $$ language 'plpgsql'; mydb= select myfunc('foo','bar'); myfunc - (foo1,bar1) (foo2,bar2) (foo3,bar3) (foo4,bar4) (4 rows) Look at the returns table (col1 type, col2 type) form. David J.
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
On 18/06/2015 13:36, Sven Geggus wrote: Hello, I supose this is simple, but I did not find a solution in the documentation. I would like to be able to do something like this: select myfunc('foo','bar'); or select myfunc(foo, bar) from foobartable; or even select myfunc(foo, bar), 'baz' as baz from foobartable; Which should return something like this: foo | bar --+-- foo1 | bar1 foo2 | bar2 foo3 | bar3 foo4 | bar4 (4 rows) So the output should be at least two columns and (usually) more than one row. What I currently have is the following, which is mostly it. Unfortunately it gives me only one column (I really need two) and I would have to create a custom type: CREATE TYPE t_foobar AS (foo text, bar text); CREATE or REPLACE FUNCTION myfunc(foo text, bar text) returns SETOF t_foobar as $$ BEGIN FOR i IN 1..4 LOOP RETURN NEXT (foo || i::text, bar || i::text); END LOOP; RETURN; END; $$ language 'plpgsql'; mydb= select myfunc('foo','bar'); You need to do: select * from myfunc('foo','bar'); Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
Raymond O'Donnell r...@iol.ie wrote: mydb= select myfunc('foo','bar'); You need to do: select * from myfunc('foo','bar'); This has been a misguided example. Reality should more likely look like this: select myfunc(col1,col2) from mytable; And it would of course be undesired if myfunc would be called twice per row. So how would this look like to avoid the function beeing called twice? Regards Sven -- Der wichtigste Aspekt, den Sie vor der Entscheidung für ein Open Source-Betriebssystem bedenken sollten, ist, dass Sie kein Windows-Betriebssystem erhalten. (von http://www.dell.de/ubuntu) /me is giggls@ircnet, http://sven.gegg.us/ on the Web -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
On Thu, Jun 18, 2015 at 7:50 AM, Chris Travers chris.trav...@gmail.com wrote: On Thu, Jun 18, 2015, 14:38 Sven Geggus li...@fuchsschwanzdomain.de wrote: Hello, I supose this is simple, but I did not find a solution in the documentation. Because you already are returning 2 columns. I would like to be able to do something like this: select myfunc('foo','bar'); or select myfunc(foo, bar) from foobartable; or even select myfunc(foo, bar), 'baz' as baz from foobartable; Which should return something like this: foo | bar --+-- foo1 | bar1 foo2 | bar2 foo3 | bar3 foo4 | bar4 (4 rows) So the output should be at least two columns and (usually) more than one row. What I currently have is the following, which is mostly it. Unfortunately it gives me only one column (I really need two) and I would have to create a custom type: CREATE TYPE t_foobar AS (foo text, bar text); CREATE or REPLACE FUNCTION myfunc(foo text, bar text) returns SETOF t_foobar as $$ BEGIN FOR i IN 1..4 LOOP RETURN NEXT (foo || i::text, bar || i::text); END LOOP; RETURN; END; $$ language 'plpgsql'; mydb= select myfunc('foo','bar'); myfunc - (foo1,bar1) (foo2,bar2) (foo3,bar3) (foo4,bar4) (4 rows) Select (myfunc('foo','bar')).*; Or Select * from myfunc('foo','bar'); this syntax: Select (myfunc('foo','bar')).*; should generally be avoided. in this case, the server would expand that to: select (myfunc('foo','bar')).foo, (myfunc('foo','bar')).bar; merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
Raymond O'Donnell r...@iol.ie writes: On 18/06/2015 13:36, Sven Geggus wrote: I would like to be able to do something like this: select myfunc('foo','bar'); or select myfunc(foo, bar) from foobartable; or even select myfunc(foo, bar), 'baz' as baz from foobartable; You need to do: select * from myfunc('foo','bar'); That's enough to expand the output from a simple function call. If you want to do something like Sven's later examples, the best way is with LATERAL: select f.*, 'baz' as baz from foobartable, lateral myfunc(foo, bar) as f; regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
On Thursday, June 18, 2015, Chris Travers chris.trav...@gmail.com wrote: Select (myfunc('foo','bar')).*; This should be avoided. Use lateral instead,or a cte a/o offset 0. My_func is evaluated twice (once per column) if called this way Or Select * from myfunc('foo','bar'); This is ok David J.
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
On Thu, Jun 18, 2015 at 9:32 AM, Sven Geggus li...@fuchsschwanzdomain.de wrote: David G. Johnston david.g.johns...@gmail.com wrote: Look at the returns table (col1 type, col2 type) form. If I got this right returns table is not what I want as I need to select from my function as a virtual table in this case. Yes, I mis-read your question. Your issue is placing the SRF (set returning function) in the select-list which causes it to be treated as a single composite-typed column. You need to place the function in after FROM or LATERAL Something like: SELECT * FROM src_tbl LATERAL my_func(src_tbl.col1, src_tbl.col2) I haven't had much experience writing lateral clauses but their benefit is that they can reference columns from other tables so you don't have to place the function in the select-list. David J.
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
David G. Johnston david.g.johns...@gmail.com wrote: Look at the returns table (col1 type, col2 type) form. If I got this right returns table is not what I want as I need to select from my function as a virtual table in this case. Regards Sven -- Thinking of using NT for your critical apps? Isn't there enough suffering in the world? (Advertisement of Sun Microsystems in Wall Street Journal) /me is giggls@ircnet, http://sven.gegg.us/ on the Web -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
On Thu, Jun 18, 2015 at 9:52 AM, Sven Geggus li...@fuchsschwanzdomain.de wrote: Raymond O'Donnell r...@iol.ie wrote: mydb= select myfunc('foo','bar'); You need to do: select * from myfunc('foo','bar'); This has been a misguided example. Reality should more likely look like this: select myfunc(col1,col2) from mytable; And it would of course be undesired if myfunc would be called twice per row. So how would this look like to avoid the function beeing called twice? 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. David J.
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
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. Sven -- Threading is a performance hack. (The Art of Unix Programming by Eric S. Raymond) /me is giggls@ircnet, http://sven.gegg.us/ on the Web -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
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.
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
Sven Geggus wrote: Using your suggestion the desired two columns are generated, but I consider this a little bit ugly: 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 What's wrong with a plain subselect? select (myfunc).* from (select myfunc(col1,col2) from mytable) f; -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
David G. Johnston david.g.johns...@gmail.com wrote: Assuming you are on 9.3+ what you want to use is LATERAL OK, how is such a query supposed to look like? assuming select myfunc(col1,col2) from mytable works as the inner select? Sven -- Software patents are the software project equivalent of land mines: Each design decision carries a risk of stepping on a patent, which can destroy your project. (Richard M. Stallman) /me is giggls@ircnet, http://sven.gegg.us/ on the Web -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows
On Thu, Jun 18, 2015 at 12:00 PM, Sven Geggus li...@fuchsschwanzdomain.de wrote: David G. Johnston david.g.johns...@gmail.com wrote: Assuming you are on 9.3+ what you want to use is LATERAL OK, how is such a query supposed to look like? assuming select myfunc(col1,col2) from mytable works as the inner select? Syntax, description, and examples for a simple lateral query are documented here: http://www.postgresql.org/docs/9.3/static/sql-select.html David J.