Thread: Need help with INOUT mis-understanding
Yes, I'm a Newbie but I really like it so far except a few
misunderstandings I have, like INOUT. (I am using 8.1 beta 3 on both
WIN and FC w/ pgadmin III.)
I cannot use multiple in/out/inouts within functions to return results.
How can I use INOUTS the right way? Here I thought I'd get an updated
value from "func2" within "func1":
-- FUNC1
CREATE OR REPLACE FUNCTION func1(INOUT var_1 int4) AS
'
DECLARE
var_X RECORD;
var_Y int4;
BEGIN
var_Y := var_1;
RAISE NOTICE ''var_1 starts as %'',var_1;
var_X := func2(var_1,var_Y);
RAISE NOTICE ''var_X Now is %'',var_X;
RAISE NOTICE ''var_1 Now is %'',var_1;
END --test__inout
'
LANGUAGE 'plpgsql' VOLATILE;
---FUNC2
CREATE OR REPLACE FUNCTION func2(INOUT var_1 int4,
INOUT var_Y int4) AS
'
BEGIN
var_1 = var_1 + var_1;
RAISE NOTICE ''var_1 in func2 is %'',var_1;
END --func2
'
LANGUAGE 'plpgsql' VOLATILE;
-----RESULTS:
NOTICE: var_1 starts as 5
NOTICE: var_1 in func2 is 10
CONTEXT: PL/pgSQL function "func1" line 7 at assignment
NOTICE: var_X Now is (10,5)
NOTICE: var_1 Now is 5
Total query runtime: 30 ms.
Data retrieval runtime: 0 ms.
1 rows retrieved.
(Shouldn't var_1 = 10 Now?)
ANY ADVICE? Where can I find out how to handle them correctly?
I have tried using Return and such but I don't get it. (Been reading
all the online and support help I can find.) Does PL/PGSQL handle
INOUTS the same as ORACLE PL/SQL?
"Troy" <troy@hendrix.biz> writes:
> Does PL/PGSQL handle
> INOUTS the same as ORACLE PL/SQL?
Probably not, if the way you seem to expect it to work is like Oracle.
An INOUT parameter isn't some sort of modifiable by-reference variable,
it's just a shorthand for declaring an IN parameter and an OUT
parameter.
regards, tom lane
Tom Lane wrote: >"Troy" <troy@hendrix.biz> writes: > > >>Does PL/PGSQL handle >>INOUTS the same as ORACLE PL/SQL? >> >> > >Probably not, if the way you seem to expect it to work is like Oracle. >An INOUT parameter isn't some sort of modifiable by-reference variable, >it's just a shorthand for declaring an IN parameter and an OUT >parameter. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > > > Tom, I have noticed this as well, if I declare OUT params I can modify them to my hearts content before they go out, however if you declare it as a INOUT you can't modify it because it is declared as a constant. I would expect the behavior of a INOUT to be ths same as OUT, not the same as a IN. There have been many times I really could have even used IN params that where not declared as CONST. How difficult would it be to have INOUT params not declared as CONST? Thanks, Tony Caduto
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> I have noticed this as well, if I declare OUT params I can modify them
> to my hearts content before they go out,
> however if you declare it as a INOUT you can't modify it because it is
> declared as a constant.
Uh, I don't think so.
/* input argument vars are forced to be CONSTANT */
if (argmode == PROARGMODE_IN)
((PLpgSQL_var *) argvariable)->isconst = true;
Can you provide a test case that misbehaves that way?
regards, tom lane
Tom Lane wrote: >Tony Caduto <tony_caduto@amsoftwaredesign.com> writes: > > >>I have noticed this as well, if I declare OUT params I can modify them >>to my hearts content before they go out, >>however if you declare it as a INOUT you can't modify it because it is >>declared as a constant. >> >> > >Uh, I don't think so. > > /* input argument vars are forced to be CONSTANT */ > if (argmode == PROARGMODE_IN) > ((PLpgSQL_var *) argvariable)->isconst = true; > >Can you provide a test case that misbehaves that way? > > regards, tom lane > > > Tom, Maybe we are mis-communicating here. So what you are saying is INOUT params are NOT constants and you can modify them in the function body? I could have sworn that did not work for me in beta1, I will try it again in beta 4 and see if things have changed. Thanks, Tony Caduto
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> So what you are saying is INOUT params are NOT constants and you can
> modify them in the function body?
If you couldn't modify them, there would be no way to return a new
value (ie, anything but the passed-in value), so it'd be pretty broken
IMHO ...
regards, tom lane
> Probably not, if the way you seem to expect it to work is like Oracle. > An INOUT parameter isn't some sort of modifiable by-reference variable, > it's just a shorthand for declaring an IN parameter and an OUT > parameter. > Thanks for the response. That makes a lot of sense but I guess I was wishing to make it work in porting all the oracle pl/sql. If I have a function with 3 INOUTs and I get a return of (30,20,1) how should I structure it so I can read only the "1" from another function call? Should I be doing SETOF? I just haven't figured how to parse results within functions.. any suggestions for me? Thank you Troy
Tom's right, As in the first message of this thread kindof shows; func2(INOUT) adds the var_1 to itself and ouputs back to func1 as the updated value. NOTICE: var_1 starts as 5 NOTICE: var_1 in func2 is 10 CONTEXT: PL/pgSQL function "func1" line 7 at assignment NOTICE: var_X Now is (10,5) --var_1 was 1st returned var So INOUTS "work" the way Tom says in PG8.1beta3 WIN XP The part I was confused about was that I thought that the value would then be modified in func1 (by func2) since it was returned using INOUT but Tom set me strait - INOUTS are nice 'shorthand'. (I waisted a lot of time not knowing this.) Thanks P.S. - Tom if the return of func2 = var_X = (10,5) how can I parse the varible out like: var_Y = var_X[1] -- first ARRAY item to get var_Y = 10? Troy H
"Troy" <troy@hendrix.biz> writes:
> P.S. - Tom if the return of func2 = var_X = (10,5) how can I parse the
> varible out like:
> var_Y = var_X[1] -- first ARRAY item
> to get var_Y = 10?
Try assigning the function result to a RECORD variable, perhaps
SELECT * INTO rec FROM foo(...);
Then you can access the record variable's fields.
regards, tom lane