Thread: Should we still require RETURN in plpgsql?
As of CVS tip, plpgsql handles output parameters, in the style
CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN sum := x + y; prod := x * y; RETURN;
END;
$$ LANGUAGE plpgsql;
The RETURN statement is kinda useless in this example, but it is still
required, because we don't allow control to fall off the end of a
plpgsql function without causing an error.
I am thinking we should allow exit by falling off the end of the
function when (a) it has output parameter(s), or (b) it is declared
"RETURNS void". Comments?
How does Oracle's PL/SQL handle this?
regards, tom lane
> I am thinking we should allow exit by falling off the end of the > function when (a) it has output parameter(s), or (b) it is declared > "RETURNS void". Comments? I agree - makes sense. Chris
On Tue, 5 Apr 2005, Tom Lane wrote: > CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ > BEGIN > sum := x + y; > prod := x * y; > RETURN; > END; > $$ LANGUAGE plpgsql; > > The RETURN statement is kinda useless in this example, but it is still > required, because we don't allow control to fall off the end of a > plpgsql function without causing an error. > > I am thinking we should allow exit by falling off the end of the > function when (a) it has output parameter(s), or (b) it is declared > "RETURNS void". Comments? The above code example do not have any RETURNS clause, does that mean that it defaults to RETURNS void? I don't see what (a) has to do with anything. The return value is independent of in/out:ness of the parameters, isn't it? -- /Dennis Björklund
Dennis Bjorklund <db@zigo.dhs.org> writes:
> On Tue, 5 Apr 2005, Tom Lane wrote:
>> CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
>> BEGIN
>> sum := x + y;
>> prod := x * y;
>> RETURN;
>> END;
>> $$ LANGUAGE plpgsql;
> The above code example do not have any RETURNS clause, does that mean that
> it defaults to RETURNS void?
No, it effectively "RETURNS record", where the particular record type is
implied by the set of output parameters. See my previous proposal.
regards, tom lane
On Tue, 2005-04-05 at 03:43, Tom Lane wrote:
> Dennis Bjorklund <db@zigo.dhs.org> writes:
> > On Tue, 5 Apr 2005, Tom Lane wrote:
> >> CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
> >> BEGIN
> >> sum := x + y;
> >> prod := x * y;
> >> RETURN;
> >> END;
> >> $$ LANGUAGE plpgsql;
>
> > The above code example do not have any RETURNS clause, does that mean that
> > it defaults to RETURNS void?
>
> No, it effectively "RETURNS record", where the particular record type is
> implied by the set of output parameters. See my previous proposal.
>
While it is useless in this example, istm it only makes things more
confusing to require return in some cases but not in others. Is there
some technical advantage to dropping it?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Robert Treat <xzilla@users.sourceforge.net> writes:
> While it is useless in this example, istm it only makes things more
> confusing to require return in some cases but not in others. Is there
> some technical advantage to dropping it?
It's about the same either way as far as the code is concerned. But
I've only written a dozen or so plpgsql functions using OUT parameters,
and I've already found having to write a useless RETURN to be tedious
(not to mention that I forgot it a couple times). I don't think I'll be
the last one complaining if we leave in the requirement.
Basically the requirement exists to make sure you don't forget to define
the return value. But when you're using OUT parameters, the existence
of a RETURN statement has nothing to do with defining the return value.
regards, tom lane
On Tue, 5 Apr 2005, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
>> While it is useless in this example, istm it only makes things more
>> confusing to require return in some cases but not in others. Is there
>> some technical advantage to dropping it?
>
> It's about the same either way as far as the code is concerned. But
> I've only written a dozen or so plpgsql functions using OUT parameters,
> and I've already found having to write a useless RETURN to be tedious
> (not to mention that I forgot it a couple times). I don't think I'll be
> the last one complaining if we leave in the requirement.
>
> Basically the requirement exists to make sure you don't forget to define
> the return value. But when you're using OUT parameters, the existence
> of a RETURN statement has nothing to do with defining the return value.
what if not require RETURN iff OUT parameter is defined ?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
> what if not require RETURN iff OUT parameter is defined ?
That's what I'm suggesting ;-)
regards, tom lane
Hello... On Tue, 05 Apr 2005 02:28:23 -0400, tgl@sss.pgh.pa.us (Tom Lane) wrote: > >How does Oracle's PL/SQL handle this? On ORACLE a FUNCTION MUST return a value. If the FUNCTION doesn't return a value Oracle give a 'hint' on FUNCTION compilation and error on SELECT function invocation: ORA-06503. When we don't want to return any result on ORACLE we must use PROCEDURE statement instead of FUNCTION. Example: ======== SQL> CREATE OR REPLACE FUNCTION F_test RETURN NUMBER IS 2 BEGIN 3 NULL; 4 END F_TEST; 5 / Function created. SQL> SELECT F_TEST FROM DUAL; SELECT TUZSA.F_TEST FROM DUAL * ERROR at line 1: ORA-06503: PL/SQL: Function returned without value ORA-06512: at "F_TEST", line 3 SQL> ======== Greetings.