Re: handling out parameter

Hello

2010/6/4 Ravi Katkar <Ravi(dot)Katkar(at)infor(dot)com>:
> Hi ,
>
>
>
> I have below function adf with inout, out parameter ,
>
>
>
> CREATE OR REPLACE FUNCTION adf(inout voutvar integer , out vVar integer)
>
>  AS
>
> $BODY$
>
> BEGIN
>
>   voutvar := 20;
>
>   vvar := 10;
>
> RETURN;
>
> END; $BODY$
>
>   LANGUAGE 'plpgsql'
>
>
>
> After compiling I get below signature of function
>

PostgreSQL doesn't compile PLpgSQL code - just validate syntax and
store source code and interface description to pg_proc table.

When function returns only one parameter, it returns some scalar data
type. The syntax isn't important. In other cases function has to
return record. One OUT param and one INOUT params are two OUT params
-> function has to return record.

Second important rule - PostgreSQL can not pass values by ref. Just
only by val. So if you want get some result, you cannot use PERFORM
statement.

CREATE OR REPLACE FUNCTION foo(IN a int, OUT b int)
AS $$
BEGIN
b := a + 10;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

...
DECLARE result int;
BEGIN
result := foo(10);
END;

...

CREATE OR REPLACE FUNCTION foo2(OUT a int, OUT b int, c int) AS $$
BEGIN
a := c + 1;
b := c + 2;
END
$$ LANGUAGE plpgsql;

DECLARE result RECORD;
BEGIN
result := foo2(20);
RAISE NOTICE 'result is % %', result.a, result.b;
END;

Regards

Pavel Stehule

>
>
> adf(integer)
>
>
>
> and return type as record.
>
>
>
> CREATE OR REPLACE FUNCTION adf(INOUT voutvar integer, OUT vvar integer)
>
>   RETURNS record AS
>
>
>
> I wanted to catch output parameter – Vvar .
>
>
>
> Below function tt , tries adf,
>
>
>
> CREATE OR REPLACE FUNCTION tt()
>
>   RETURNS VOID AS
>
> $BODY$
>
> DECLARE
>
>  ii  integer;
>
>  vout integer;
>
> BEGIN
>
>   --vvar := 10;
>
>   vout := 10;
>
>   perform adf(vout)  ;
>
> RETURN;
>
> END; $BODY$
>
>   LANGUAGE 'plpgsql';
>
>
>
>
>
> I have a couple of questions on above function
>
>
>
> 1) Why the return type is record after compiling?
>
> 2) How to catch the return value of out parameter for above case value of
>  vVar.
>
>
>
>
>
> Thanks,
>
> Ravi Katkar
>
>