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

Search Discussions

  • Christopher Kings-Lynne at Apr 5, 2005 at 6:40 am

    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
  • Dennis Bjorklund at Apr 5, 2005 at 7:04 am

    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
  • Tom Lane at Apr 5, 2005 at 7:43 am

    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
  • Robert Treat at Apr 5, 2005 at 1:01 pm

    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
  • Tom Lane at Apr 5, 2005 at 3:22 pm

    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
  • Oleg Bartunov at Apr 5, 2005 at 3:34 pm

    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
  • Tom Lane at Apr 5, 2005 at 3:51 pm

    Oleg Bartunov writes:
    what if not require RETURN iff OUT parameter is defined ?
    That's what I'm suggesting ;-)

    regards, tom lane
  • Terry Yapt at Apr 5, 2005 at 7:51 am
    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedApr 5, '05 at 6:28a
activeApr 5, '05 at 3:51p
posts9
users6
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase