Re: PROPOSAL FE/BE extension to handle IN/OUT parameters - Mailing list pgsql-hackers

From Tom Lane
Subject Re: PROPOSAL FE/BE extension to handle IN/OUT parameters
Date
Msg-id 18589.1119390559@sss.pgh.pa.us
Whole thread Raw
In response to Re: PROPOSAL FE/BE extension to handle IN/OUT parameters  (Dave Cramer <pg@fastcrypt.com>)
Responses Re: PROPOSAL FE/BE extension to handle IN/OUT parameters
Re: PROPOSAL FE/BE extension to handle IN/OUT parameters
List pgsql-hackers
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
functionin the catalogs, does      * disambiguation for polymorphic functions, handles inheritance, and      * returns
thefuncid and type and set or singleton status of the
 


pgsql-hackers by date:

Previous
From: Josh Berkus
Date:
Subject: Re: Why is checkpoint so costly?
Next
From: Tom Lane
Date:
Subject: Re: pg_terminate_backend idea