PROPOSAL FE/BE extension to handle IN/OUT parameters

Dave Cramer <pg@fastcrypt.com> writes:

The current situation with IN/OUT parameters requires that
considerable juggling is required on the client end to not pass the
OUT parameters in the query. This could be alleviated by adding two
messages for stored procedure calls
1) PrepareCall which sent the types, and direction of the parameters
2) BindCall which sends the binds the parameters to the above

Is it possible to get this into 8.1, or is this a total non-starter

Changing the protocol is a nonstarter at this late date in the release
cycle. I previously offered you a hack that would accomplish the same
thing (or at least it looks like the same thing to me): ignore
parameters of type VOID when looking up a function. Is that unusable
from your end?

regards, tom lane

Dave Cramer <pg@fastcrypt.com> writes:

Yeah, I think that might work if I understand it correctly.
Assuming I would be able to prepare, and bind all the parameters, and
the OUT parameters
would be ignored.

This is what I've got in mind:

regression=# create function myfunc(f1 int, f2 int, out sum int,
out prod int)
regression-# language plpgsql strict immutable as $$
regression$# begin
regression$# sum := f1 + f2;
regression$# prod := f1 * f2;
regression$# end$$;
CREATE FUNCTION
regression=# select * from myfunc(11,22);
sum | prod
-----+------
33 | 242
(1 row)

Using PREPARE/EXECUTE as a SQL-level substitute for Parse/Bind/Execute
messages, the CVS-tip behavior is

regression=# prepare foo(int,int,void,void) as
regression-# select * from myfunc($1,$2,$3,$4);
ERROR: function myfunc(integer, integer, void, void) does not exist

and with the attached patch you'd get

regression=# prepare foo(int,int,void,void) as
regression-# select * from myfunc($1,$2,$3,$4);
PREPARE
regression=# execute foo(11,22,null,null);
sum | prod
-----+------
33 | 242
(1 row)

Does that solve your problem?

regards, tom lane

*** src/backend/parser/parse_func.c.orig    Mon May 30 21:03:23 2005
--- src/backend/parser/parse_func.c    Tue Jun 21 17:43:51 2005
***************
*** 64,69 ****
--- 64,70 ----
Oid            rettype;
Oid            funcid;
ListCell   *l;
+     ListCell   *nextl;
Node       *first_arg = NULL;
int            nargs = list_length(fargs);
int            argn;
***************
*** 85,90 ****
--- 86,118 ----
errmsg("cannot pass more than %d arguments to a  
function",
FUNC_MAX_ARGS)));

+     /*
+      * Extract arg type info in preparation for function lookup.
+      *
+      * If any arguments are Param markers of type VOID, we  
discard them
+      * from the parameter list.  This is a hack to allow the JDBC  
driver
+      * to not have to distinguish "input" and "output" parameter  
symbols
+      * while parsing function-call constructs.  We can't use  
foreach()
+      * because we may modify the list ...
+      */
+     argn = 0;
+     for (l = list_head(fargs); l != NULL; l = nextl)
+     {
+         Node       *arg = lfirst(l);
+         Oid            argtype = exprType(arg);
+
+         nextl = lnext(l);
+
+         if (argtype == VOIDOID && IsA(arg, Param))
+         {
+             fargs = list_delete_ptr(fargs, arg);
+             nargs--;
+             continue;
+         }
+
+         actual_arg_types[argn++] = argtype;
+     }
+
if (fargs)
{
first_arg = linitial(fargs);
***************
*** 99,105 ****
*/
if (nargs == 1 && !agg_star && !agg_distinct && list_length 
(funcname) == 1)
{
!         Oid            argtype = exprType(first_arg);

if (argtype == RECORDOID || ISCOMPLEX(argtype))
{
--- 127,133 ----
*/
if (nargs == 1 && !agg_star && !agg_distinct && list_length 
(funcname) == 1)
{
!         Oid            argtype = actual_arg_types[0];

if (argtype == RECORDOID || ISCOMPLEX(argtype))
{
***************
*** 117,134 ****
}

/*
!      * Okay, it's not a column projection, so it must really be a
!      * function. Extract arg type info in preparation for  
function lookup.
!      */
!     argn = 0;
!     foreach(l, fargs)
!     {
!         Node       *arg = lfirst(l);
!
!         actual_arg_types[argn++] = exprType(arg);
!     }
!
!     /*
* func_get_detail looks up the function in the catalogs, does
* disambiguation for polymorphic functions, handles  
inheritance, and
* returns the funcid and type and set or singleton status of  
the
--- 145,151 ----
}

/*
! * Okay, it's not a column projection, so it must really be a
function.
* func_get_detail looks up the function in the catalogs, does
* disambiguation for polymorphic functions, handles
inheritance, and
* returns the funcid and type and set or singleton status of
the

---------------------------(end of
broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org

Dave Cramer <pg@fastcrypt.com> writes:

Yeah, I think that might work if I understand it correctly.
Assuming I would be able to prepare, and bind all the parameters, and
the OUT parameters
would be ignored.

This is what I've got in mind:

regression=# create function myfunc(f1 int, f2 int, out sum int,
out prod int)
regression-# language plpgsql strict immutable as $$
regression$# begin
regression$# sum := f1 + f2;
regression$# prod := f1 * f2;
regression$# end$$;
CREATE FUNCTION
regression=# select * from myfunc(11,22);
sum | prod
-----+------
33 | 242
(1 row)

Using PREPARE/EXECUTE as a SQL-level substitute for Parse/Bind/Execute
messages, the CVS-tip behavior is

regression=# prepare foo(int,int,void,void) as
regression-# select * from myfunc($1,$2,$3,$4);
ERROR: function myfunc(integer, integer, void, void) does not exist

and with the attached patch you'd get

regression=# prepare foo(int,int,void,void) as
regression-# select * from myfunc($1,$2,$3,$4);
PREPARE
regression=# execute foo(11,22,null,null);
sum | prod
-----+------
33 | 242
(1 row)

Does that solve your problem?

regards, tom lane

*** src/backend/parser/parse_func.c.orig    Mon May 30 21:03:23 2005
--- src/backend/parser/parse_func.c    Tue Jun 21 17:43:51 2005
***************
*** 64,69 ****
--- 64,70 ----
Oid            rettype;
Oid            funcid;
ListCell   *l;
+     ListCell   *nextl;
Node       *first_arg = NULL;
int            nargs = list_length(fargs);
int            argn;
***************
*** 85,90 ****
--- 86,118 ----
errmsg("cannot pass more than %d arguments to a  
function",
FUNC_MAX_ARGS)));

+     /*
+      * Extract arg type info in preparation for function lookup.
+      *
+      * If any arguments are Param markers of type VOID, we  
discard them
+      * from the parameter list.  This is a hack to allow the JDBC  
driver
+      * to not have to distinguish "input" and "output" parameter  
symbols
+      * while parsing function-call constructs.  We can't use  
foreach()
+      * because we may modify the list ...
+      */
+     argn = 0;
+     for (l = list_head(fargs); l != NULL; l = nextl)
+     {
+         Node       *arg = lfirst(l);
+         Oid            argtype = exprType(arg);
+
+         nextl = lnext(l);
+
+         if (argtype == VOIDOID && IsA(arg, Param))
+         {
+             fargs = list_delete_ptr(fargs, arg);
+             nargs--;
+             continue;
+         }
+
+         actual_arg_types[argn++] = argtype;
+     }
+
if (fargs)
{
first_arg = linitial(fargs);
***************
*** 99,105 ****
*/
if (nargs == 1 && !agg_star && !agg_distinct && list_length 
(funcname) == 1)
{
!         Oid            argtype = exprType(first_arg);

if (argtype == RECORDOID || ISCOMPLEX(argtype))
{
--- 127,133 ----
*/
if (nargs == 1 && !agg_star && !agg_distinct && list_length 
(funcname) == 1)
{
!         Oid            argtype = actual_arg_types[0];

if (argtype == RECORDOID || ISCOMPLEX(argtype))
{
***************
*** 117,134 ****
}

/*
!      * Okay, it's not a column projection, so it must really be a
!      * function. Extract arg type info in preparation for  
function lookup.
!      */
!     argn = 0;
!     foreach(l, fargs)
!     {
!         Node       *arg = lfirst(l);
!
!         actual_arg_types[argn++] = exprType(arg);
!     }
!
!     /*
* func_get_detail looks up the function in the catalogs, does
* disambiguation for polymorphic functions, handles  
inheritance, and
* returns the funcid and type and set or singleton status of  
the
--- 145,151 ----
}

/*
! * Okay, it's not a column projection, so it must really be a
function.
* func_get_detail looks up the function in the catalogs, does
* disambiguation for polymorphic functions, handles
inheritance, and
* returns the funcid and type and set or singleton status of
the