Thread: Need help with INOUT mis-understanding

Need help with INOUT mis-understanding

From
"Troy"
Date:
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?


Re: Need help with INOUT mis-understanding

From
Tom Lane
Date:
"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

Re: Need help with INOUT mis-understanding

From
Tony Caduto
Date:
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


Re: Need help with INOUT mis-understanding

From
Tom Lane
Date:
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

Re: Need help with INOUT mis-understanding

From
Tony Caduto
Date:
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

Re: Need help with INOUT mis-understanding

From
Tom Lane
Date:
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

Re: Need help with INOUT mis-understanding

From
"Troy"
Date:
> 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


Re: Need help with INOUT mis-understanding

From
"Troy"
Date:
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


Re: Need help with INOUT mis-understanding

From
Tom Lane
Date:
"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