Thread: Functions returning more than one value

Functions returning more than one value

From
"Chris Ruprecht"
Date:
Hi all,

How can I get more than one value back from a function?

I have a situation here, where a function needs to return a value - but also
needs to indicate from which type of record the value comes.

The most elegant would be something like the 2 functions listed below. They
don't work, since v_val3 and v_val4 are treated as constants in test2() and
can not be changed. Is there a way this can make it into a future release?

Best regards,
Chris



drop    function    test1();
create  function    test1() returns integer as '
declare   v_res   boolean;   v_val1  integer;   v_val2  integer;

begin   v_val1  := 1;   v_val2  := 2;   v_res   := test2( v_val1, v_val2 );   return v_val1 + v_val2;
end;

' language 'plpgsql';

drop    function    test2(integer, integer);
create  function    test2(integer, integer) returns boolean as '

declare
   v_val3  alias for $1;   v_val4  alias for $2;

begin   v_val3  := 3;   v_val4  := 4;   return true;
end;

' language 'plpgsql';




_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com



Re: Functions returning more than one value

From
"Chris Ruprecht"
Date:
Josh,

the two functions are just a tiny example of what I want to do which is:
call a function with 2 or more arguments (v_val1 and v_val2).

The called function (test2() in the example) will MODIFY the two arguments
and then return a true value. After test2() has run, the value of the two
arguments 'has changed' to what test2() has assigned to them.

I know that this might not be good programming practice since many
applications rely on functions NOT changing the variables which get passed
to them as arguments. maybe if we could specially declare them as
'changeable' parameters ...

The functionality of 'returns record' is nice to have but it would not help
me in this case. I would have to create a record each time I want to pass
more than one value back to the calling procedure. This means 'disk access'
which is another word for 'this is going to slow down my program'. But this
reminds me: where are temp tables kept? On disk or in memory? - that might
be a way out of the dilemma.


Best regards,
Chris

----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "Chris Ruprecht" <chrup999@yahoo.com>
Sent: Wednesday, August 08, 2001 10:06 AM
Subject: Re: [SQL] Functions returning more than one value


> Chris,
>
> > How can I get more than one value back from a function?
>
> I assume that you're looking for a workaround here.  You've been on the
> list long enough to know that we're all waiting for record-returning
> ability in 7.2, 7.3 or more likely 8.0.
>
> > The most elegant would be something like the 2 functions listed
> > below. They
> > don't work, since v_val3 and v_val4 are treated as constants in
> > test2() and
> > can not be changed. Is there a way this can make it into a future
> > release?
>
> I'm *really* confused.  These functions seem to have nothing to do with
> your first question.  Mind documenting, line-by-line, what you're trying
> to do with the two functions you provided?  I can't puzzle it out from
> your code.
>
> -Josh
>
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                        Josh Berkus
>   Complete information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco
>


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com



Re: Functions returning more than one value

From
"Josh Berkus"
Date:
Chris,

> The called function (test2() in the example) will MODIFY the two
> arguments
> and then return a true value. After test2() has run, the value of the
> two
> arguments 'has changed' to what test2() has assigned to them.

I understand, now.  You're trying to replicate the functionality
provided in programming languages using By Reference arguments.

I could suggest a number of workarounds.  However, I think rather that
you should give serious consideration to *not* doing this in PL/pgSQL
but doing it in a full-featured procedural (and/or Object Oriented)
language instead.  Java, Python, Perl and even 4GL would provide you
with more robust functionality.

For example, what you want is easily done in Java just by passing the
input parameters as By Reference.

Opinions on Middleware languages, anyone?

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 


Re: Functions returning more than one value

From
"Grigoriy G. Vovk"
Date:
May be, in a far future, will be better to have a stored procedures which
will able to accept input arguments and output arguments?


Aug 8, 09:26 -0700, Josh Berkus wrote:

> Chris,
>
> > The called function (test2() in the example) will MODIFY the two
> > arguments
> > and then return a true value. After test2() has run, the value of the
> > two
> > arguments 'has changed' to what test2() has assigned to them.
>
> I understand, now.  You're trying to replicate the functionality
> provided in programming languages using By Reference arguments.
>
> I could suggest a number of workarounds.  However, I think rather that
> you should give serious consideration to *not* doing this in PL/pgSQL
> but doing it in a full-featured procedural (and/or Object Oriented)
> language instead.  Java, Python, Perl and even 4GL would provide you
> with more robust functionality.
>
> For example, what you want is easily done in Java just by passing the
> input parameters as By Reference.
>
> Opinions on Middleware languages, anyone?
>
> -Josh Berkus
>
>

my best regards,
----------------
Grigoriy G. Vovk