Thread: PROPOSAL FE/BE extension to handle IN/OUT parameters
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 While I have this working with the jdbc driver, the problem I foresee is that when we do finally implement something like what we have above. The current driver will be quite difficult to maintain. Additionally it will be difficult with the current scheme to return out parameters and a result set. Is it possible to get this into 8.1, or is this a total non-starter Dave Cramer davec@postgresintl.com www.postgresintl.com ICQ #14675561 jabber davecramer@jabber.org ph (519 939 0336 )
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
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. FWIW, I proposed adding to the protocol, not modifying the existing messages, so it would be backward compatible and not break existing clients. Dave On 21-Jun-05, at 5:14 PM, Tom Lane wrote: > 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.
> FWIW, I proposed adding to the protocol, not modifying the existing
> messages, so it would be backward compatible and not break existing
> clients.
What I have in mind shouldn't break any existing clients either.
There is no use for VOID parameter symbols at the moment, so assigning
a special behavior to them won't break any existing code.
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
functionin the catalogs, does * disambiguation for polymorphic functions, handles inheritance, and * returns
thefuncid and type and set or singleton status of the
I think it makes my code cleaner, I'll give you an update tomorrow
Dave
On 21-Jun-05, at 5:49 PM, Tom Lane wrote:
> 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
>
>
Tom,
This will work just great, please go ahead, and I'll adjust the
driver accordingly
Dave
On 21-Jun-05, at 5:49 PM, Tom Lane wrote:
> 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
>
>