Thread: pl/pgsql function out parameters

Hi there,

Does anyone know if out parameters are supported in pl/pgsql functions?

What I mean is something like this:

create function f(out int4) returns text as
'
    select $1 = 3
    return ""
' language 'plpgsql';

db ==> declare i int4;
db ==> select f(i);
db ==> select i;

i
==
3


I have scanned most of the plsql documentation I can lay hands on
without much joy
- mailing list archives
- User guide that is shipped with the postgres 7.02
- Programmer's guide shipped with release 7.02
- I notice that in the jdbc CallableStatement implementation,
registerOutpurParameter merely throws a notImplemented exception - Is
this a hint?
- Bruce Momjian's book - There is a chapter on functions and triggers,
but I cannot seem to find this mentioned anywhere.

If this is not doable, could someone please confirm that so I should
stop looking. On the other hand, if anyone out there has an idea how to
accomplish this, please, please help.

Kind regards,

Richard.

> Does anyone know if out parameters are supported in pl/pgsql functions?

Yes. They are not supported. I've got patches ready to submit which
recognize the IN, OUT and INOUT keywords defined in SQL99, but the
patches will just throw an explicit error if you specify an OUT/INOUT
parameter.

btw, everyone: any objections to or comments on the above?

                       - Thomas

Found it!
include/config.h.in
#define INDEX_MAX_KEYS 8
----- Original Message ----- 
From: "Thomas Lockhart" <lockhart@alumni.caltech.edu>
To: "Grigori Soloviov" <grisha@iqchoice.com>
Sent: Thursday, July 06, 2000 6:30 PM
Subject: Re: [HACKERS] pl/pgsql function out parameters


> > Don't you know how to make it take more than 8 parameters?
> 
> In the current development tree, the limit is higher than 8 parameters.
> My recollection is that in earlier code the "8 parameter limit" is
> fairly difficult to work around. Not sure if 7.0 makes it easier; look
> for a hardcoded #define in include/config.h.
> 
>                   - Thomas
> 

Thomas Lockhart wrote:

> > Does anyone know if out parameters are supported in pl/pgsql functions?
>
> Yes. They are not supported. I've got patches ready to submit which
> recognize the IN, OUT and INOUT keywords defined in SQL99, but the
> patches will just throw an explicit error if you specify an OUT/INOUT
> parameter.
>
> btw, everyone: any objections to or comments on the above?
>
>                        - Thomas

Yes, I would like to add my $2/100. I do not know what the historical trail
leading to your functions is, but in the context of a function you normally
would not want to define IN/OUT parameters (in Oracle PG/SQL it is
forbidden (Oracle PL/SQL is clearly derived from ADA)).
This forces some discipline on the programmer. I want say that you need
to define something as a subroutine, but wouldn't the following be better :
- If RETURN is used, then it is a function : forbid IN/OUT parameters
- If there is an IN/OUT parameter in the declaration, return type of the
   function may only be opaque

Jurgen