From: | Sven Geggus <lists(at)fuchsschwanzdomain(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | PL/pgSQL: How to return two columns and multiple rows |
Date: | 2015-06-18 12:36:41 |
Message-ID: | 20150618123641.GA31079@geggus.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)ircnet, http://sven.gegg.us/ on the Web
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2015-06-18 12:44:55 | Re: PL/pgSQL: How to return two columns and multiple rows |
Previous Message | Maila Fatticcioni | 2015-06-18 12:13:03 | Re: My postgres is not logging anymore |