Thread: Should we still require RETURN in plpgsql?

From:
Tom Lane
Date:

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


From:
Christopher Kings-Lynne
Date:

> 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


From:
Dennis Bjorklund
Date:

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



From:
Tom Lane
Date:

Dennis Bjorklund <> 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


From:
Robert Treat
Date:

On Tue, 2005-04-05 at 03:43, Tom Lane wrote:
> Dennis Bjorklund <> 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



From:
Tom Lane
Date:

Robert Treat <> 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


From:
Oleg Bartunov
Date:

On Tue, 5 Apr 2005, Tom Lane wrote:

> Robert Treat <> 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 )
>
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: , http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


From:
Tom Lane
Date:

Oleg Bartunov <> writes:
> what if not require RETURN iff OUT parameter is defined ?

That's what I'm suggesting ;-)
        regards, tom lane


From:
Terry Yapt
Date:

Hello...

On Tue, 05 Apr 2005 02:28:23 -0400,  (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.