Обсуждение: 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 > > > > ____________________________________________________________________________________ > 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 > 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 > -- > 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 > -- > 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