PL/pgSQL and SETOF
Hi,
I am having trouble getting a really simple PL/pgSQL function to work.
I am beginning to wonder if there is not a bug somewhere, or if Postgresql's
type system is not broken. Anyway, suppose I have the following table and
type defined:
CREATE TABLE items
(
item_id int,
item_name text,
item_etc text
);
CREATE TYPE simple_item_t AS
(
item_id int,
item_name text
);
It's easy to create a SQL function that returns a set of simple items:
CREATE FUNCTION get_items ()
RETURNS SETOF simple_item_t
LANGUAGE sql STABLE AS
$$
SELECT item_id, item_name FROM items;
$$;
Now, all I want is to create the equivalent PL/pgSQL function. Nothing
more, nothing less. This is the simplest version I can come up with:
CREATE FUNCTION get_items2 ()
RETURNS SETOF simple_item_t
LANGUAGE plpgsql STABLE AS
$$
DECLARE
item simple_item_t%ROWTYPE;
BEGIN
FOR item IN SELECT item_id, item_name FROM items
LOOP
RETURN NEXT item;
END LOOP;
RETURN;
END
$$;
Unfortunately it doesn't work! Postgresql complains that "set-valued
function called in context that cannot accept a set". Anyway, what am
I doing wrong, and what is the simplest way of translating get_items
in PL/pgSQL?
Thanks in advance!
C.S.
____________________________________________________________________________________
Get easy, one-click access to your favorites.
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs
On Fri, Nov 30, 2007 at 09:09:28AM -0800, Cultural Sublimation wrote:
Postgresql complains that "set-valued
function called in context that cannot accept a set". Anyway, what am
I doing wrong, and what is the simplest way of translating get_items
in PL/pgSQL?
I would guess you're calling it like:
SELECT get_items2();
whereas, you should call set returning functions like:
SELECT * FROM get_items2();
Sam
Hello
Unfortunately it doesn't work! Postgresql complains that "set-valued
function called in context that cannot accept a set". Anyway, what am
I doing wrong, and what is the simplest way of translating get_items
in PL/pgSQL?Thanks in advance!
C.S.
try
select * from get_items2()
Regards
Pavel Stehule
Show quoted text
____________________________________________________________________________________
Get easy, one-click access to your favorites.
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
I would guess you're calling it like:
SELECT get_items2();
whereas, you should call set returning functions like:
SELECT * FROM get_items2();
Hi,
Yeah, that was the problem -- thanks!
But still on that subject: is my version of get_items2 the simplest
that is possible in PL/pgSQL? It seems awfully verbose compared to
the SQL version...
Regards,
C.S.
____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
On 30/11/2007, Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:
I would guess you're calling it like:
SELECT get_items2();
whereas, you should call set returning functions like:
SELECT * FROM get_items2();
Hi,
Yeah, that was the problem -- thanks!
But still on that subject: is my version of get_items2 the simplest
that is possible in PL/pgSQL? It seems awfully verbose compared to
the SQL version...
no. Your simplest version is historic relict and is available only in
sql language. I am not sure, maybe in C language too.
Pavel
Show quoted text
Regards,
C.S.____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Pavel Stehule escribió:
On 30/11/2007, Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:
But still on that subject: is my version of get_items2 the simplest
that is possible in PL/pgSQL? It seems awfully verbose compared to
the SQL version...no. Your simplest version is historic relict and is available only in
sql language. I am not sure, maybe in C language too.
But isn't the case that on 8.3 you would be able to rewrite it more
easily using RETURN QUERY?
--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
"Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)
On 30/11/2007, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
Pavel Stehule escribió:
On 30/11/2007, Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:
But still on that subject: is my version of get_items2 the simplest
that is possible in PL/pgSQL? It seems awfully verbose compared to
the SQL version...no. Your simplest version is historic relict and is available only in
sql language. I am not sure, maybe in C language too.But isn't the case that on 8.3 you would be able to rewrite it more
easily using RETURN QUERY?
yes. sure. But it is different question than call SRF function in
normal context.
Pavel
Show quoted text
--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
"Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)
Pavel Stehule escribió:
On 30/11/2007, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
Pavel Stehule escribi�:
On 30/11/2007, Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:
But still on that subject: is my version of get_items2 the simplest
that is possible in PL/pgSQL? It seems awfully verbose compared to
the SQL version...no. Your simplest version is historic relict and is available only in
sql language. I am not sure, maybe in C language too.But isn't the case that on 8.3 you would be able to rewrite it more
easily using RETURN QUERY?yes. sure. But it is different question than call SRF function in
normal context.
Hmm, you are misunderstanding Cultural or I am. I think his verbosity
comment is not about the way the function is called, but rather about
the function body itself.
--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"Before you were born your parents weren't as boring as they are now. They
got that way paying your bills, cleaning up your room and listening to you
tell them how idealistic you are." -- Charles J. Sykes' advice to teenagers
On 30/11/2007, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
Pavel Stehule escribió:
On 30/11/2007, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
Pavel Stehule escribió:
On 30/11/2007, Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:
But still on that subject: is my version of get_items2 the simplest
that is possible in PL/pgSQL? It seems awfully verbose compared to
the SQL version...no. Your simplest version is historic relict and is available only in
sql language. I am not sure, maybe in C language too.But isn't the case that on 8.3 you would be able to rewrite it more
easily using RETURN QUERY?yes. sure. But it is different question than call SRF function in
normal context.Hmm, you are misunderstanding Cultural or I am. I think his verbosity
comment is not about the way the function is called, but rather about
the function body itself.
It's my English. I am sorry. You have true.
Thank you for correction
Pavel
Show quoted text
--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"Before you were born your parents weren't as boring as they are now. They
got that way paying your bills, cleaning up your room and listening to you
tell them how idealistic you are." -- Charles J. Sykes' advice to teenagers
On Nov 30, 2007 1:18 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
On 30/11/2007, Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:
But still on that subject: is my version of get_items2 the simplest
that is possible in PL/pgSQL? It seems awfully verbose compared to
the SQL version...no. Your simplest version is historic relict and is available only in
sql language. I am not sure, maybe in C language too.
It is extremely useful to be able call functions in this way. I
really wish it were possible to do this with pl/sql functions as
well...
merlin
Hello
no. Your simplest version is historic relict and is available only in
sql language. I am not sure, maybe in C language too.It is extremely useful to be able call functions in this way. I
really wish it were possible to do this with pl/sql functions as
well...merlin
where is this form better, than standard form? It's similar to CALL
statement, but I am sorry, it's inconsistent, and it do problems
mainly for beginners.
Regards
Pavel
On Dec 3, 2007 3:31 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
no. Your simplest version is historic relict and is available only in
sql language. I am not sure, maybe in C language too.It is extremely useful to be able call functions in this way. I
really wish it were possible to do this with pl/sql functions as
well...where is this form better, than standard form? It's similar to CALL
statement, but I am sorry, it's inconsistent, and it do problems
mainly for beginners.
as you noted, a principal draw of this syntax isf invoking functions
like procedures. if we had true stored procedures, this argument
would probably go away. However, it also allows expressing some
complex queries in a simpler, faster way. The payoff here is in some
really tricky cases. Here is an example (note the expansion of the
set via the SRF 'explode array'). This isn't the only way to do this,
but is the best in PostgreSQL terms (AFAICT).
select q.item_lookup, is_child, p.* from item p
join
(
select
item_id as item_lookup,
false as is_child,
explode_array(parents) as item_id
from item
union all
select
l.item_id as item_lookup,
true as is_child,
r.item_id
from item l
join item r on r.parents between (l.parents || 0) and (l.parents
|| 2147483647)
) q using (item_id);
for the full context where I used that query, go to
http://merlinmoncure.blogspot.com/2007/09/one-of-my-favorite-problems-in.html
The really odd thing to me is that multiple SRF as field functions
expand to LCM number of rows. OTOH, this gives us a brain busting
(seasonally themed) way to count up the 12 days of Christmas :D
create or replace function f(i int) returns setof int as
$$
select generate_series(1, $1);
$$ language sql;
select sum((v).f) from (select f(f(12))) v;
sum
-----
364
(1 row)
merlin