Re: PL/pgSQL: How to return two columns and multiple rows

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');

Regards

Sven

--
Exploits and holes are a now a necessary protection against large
corporate interests. (Alan Cox)

/me is giggls(at)ircnet, http://sven.gegg.us/ on the Web

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general