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 <lists@fuchsschwanzdomain.de>:

On Thursday, June 18, 2015, Sven Geggus <lists@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.

On Thu, Jun 18, 2015, 14:38 Sven Geggus <lists@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

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
rod@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

"Raymond O'Donnell" <rod@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

On Thu, Jun 18, 2015 at 7:50 AM, Chris Travers <chris.travers@gmail.com> wrote:

On Thu, Jun 18, 2015, 14:38 Sven Geggus <lists@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

On Thursday, June 18, 2015, Chris Travers <chris.travers@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.

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

On Thu, Jun 18, 2015 at 9:32 AM, Sven Geggus <lists@fuchsschwanzdomain.de>
wrote:

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

Pavel Stehule <pavel.stehule@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

Raymond O'Donnell <rod@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

On Thu, Jun 18, 2015 at 9:52 AM, Sven Geggus <lists@fuchsschwanzdomain.de>
wrote:

Raymond O'Donnell <rod@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.
​

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.

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

On Thu, Jun 18, 2015 at 10:31 AM, Sven Geggus <lists@fuchsschwanzdomain.de>
wrote:

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.

​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.
​

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 Herrera http://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

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

On Thu, Jun 18, 2015 at 12:00 PM, Sven Geggus <lists@fuchsschwanzdomain.de>
wrote:

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