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, 14:38 Sven Geggus <lists@xxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[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