From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Chris Travers <chris(dot)travers(at)gmail(dot)com> |
Cc: | Sven Geggus <lists(at)fuchsschwanzdomain(dot)de>, PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: PL/pgSQL: How to return two columns and multiple rows |
Date: | 2015-06-18 13:10:13 |
Message-ID: | CAHyXU0ycJsPnyw_xik_j2TPky45La5yZKQ2zWm06uybSLmtsXg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jun 18, 2015 at 7:50 AM, Chris Travers <chris(dot)travers(at)gmail(dot)com> wrote:
>
> On Thu, Jun 18, 2015, 14:38 Sven Geggus <lists(at)fuchsschwanzdomain(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2015-06-18 13:15:34 | Re: PL/pgSQL: How to return two columns and multiple rows |
Previous Message | Tom Lane | 2015-06-18 13:03:01 | Re: PL/pgSQL: How to return two columns and multiple rows |