Обсуждение: Functions returning more than one value
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
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
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
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