Thread: implicit vs. explicit RETURN when OUT is used
I've read this CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ BEGIN sum := x + y; prod := x * y; END; $$ LANGUAGE plpgsql; at http://www.postgresql.org/docs/8.1/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES what if I need explicit RETURN to get out of the function if I don't want to create a type and still have control of the types of the returned values (that means avoiding any*)? eg. if(found) then RETURN Afunction(); else RETURN Bfunction(); end if; currently I solved the issue this way: create or replace function testA(out _BasketID1 int, out _BasketID2 int) as $$ begin _BasketID1:=1; _BasketID2:=2; return; end; $$ language plpgsql; create or replace function testB(out _BasketID1 int, out _BasketID2 int) as $$ begin select into _BasketID1,_BasketID2 * from testA(); return; end; $$ language plpgsql; But when I switch to select into _BasketID1,_BasketID2 _BasketID1,_BasketID2 from testA(); nothing get back from testB(). That obliges me to match returning parameters, that may not always be the case. Any other way to return *controlled* composite types without declaring a composite type explicitly? thx -- Ivan Sergio Borgonovo http://www.webthatworks.it
On Fri, 4 Jan 2008 09:38:35 +0100 Ivan Sergio Borgonovo <mail@webthatworks.it> wrote: > create or replace function testA(out _BasketID1 int, out _BasketID2 > int) as $$ > begin > _BasketID1:=1; > _BasketID2:=2; > return; > end; > $$ language plpgsql; > > create or replace function testB(out _BasketID1 int, out _BasketID2 > int) as > $$ > begin > select into _BasketID1,_BasketID2 * from testA(); > return; > end; > $$ language plpgsql; > > But when I switch to > > select into _BasketID1,_BasketID2 _BasketID1,_BasketID2 from > testA(); > > nothing get back from testB(). OK switching to: select _BasketID1,_BasketID2 into _BasketID1,_BasketID2 from testA(); still didn't return anything but changing the def to create or replace function testB(out _BasketID1 int, out _BasketID2 int) as $$ begin select a._BasketID1,a._BasketID2 into _BasketID1z,_BasketID2z from testA() as a; return; end; $$ language plpgsql; did work. here you get the same in a maybe more readable, cut&pastable format: http://www.webthatworks.it/d1/node/page/returning_composite_type_postgresql_functions_without_declaring_explicitly_composite_type -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > But when I switch to > select into _BasketID1,_BasketID2 _BasketID1,_BasketID2 from testA(); > nothing get back from testB(). I think you've forgotten that plpgsql variables will be substituted for, wherever they appear. The above is just an extremely expensive form of _BasketID1 := _BasketID1; _BasketID2 := _BasketID2; ie, a big no-op. The general rule of thumb is not to name plpgsql parameters or variables the same as fields you'll need to reference in the queries in the function. regards, tom lane
On Sun, 06 Jan 2008 02:47:17 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > > But when I switch to > > select into _BasketID1,_BasketID2 _BasketID1,_BasketID2 from > > testA(); nothing get back from testB(). > > I think you've forgotten that plpgsql variables will be substituted > for, wherever they appear. The above is just an extremely expensive > form of > _BasketID1 := _BasketID1; > _BasketID2 := _BasketID2; > ie, a big no-op. > The general rule of thumb is not to name plpgsql parameters or > variables the same as fields you'll need to reference in the > queries in the function. I've found a solution that is somehow elegant and unexpected: "aliasing" the function name. select a.field1, a.field2 into field1, field2 from testfunc() as a; The online documentation doesn't give postgresql the honour it deserves. Things like this can't be found even on voluminous books as "The Postgresql reference manual". If you're not a Postgres guru you may think that pg doesn't shine for anything other than things just "data paranoids" can appreciate but force you to live with "strangeness" like "ILIKE" and no syntactical sugar. It's plenty of syntactical sugar. It is just hidden in poetry like this: "Except for the INTO clause, the SELECT statement is the same as a normal SQL SELECT command and can use its full power." http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-SELECT-INTO -- Ivan Sergio Borgonovo http://www.webthatworks.it