Обсуждение: PL/pgSQL: How to return two columns and multiple rows

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

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)

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

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

"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

Select (myfunc('foo','bar')).*;

Or
Select * from myfunc('foo','bar');

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

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

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

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

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

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