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:
--
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