Re: proof concept: do statement parametrization

2010/7/4 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> This whole proposal strikes me as premature. What we need is some
>> experience from the field in using DO before we can sensibly decide how
>> it should be extended. And we won't get that until 9.0 has been released
>> and used for a while.
>
> +1.
>
> What strikes me about this proposal is that there isn't any way to pass
> parameter strings without worrying about how to escape them; which means
> that the actual functionality gain over 9.0 is at best rather limited.
>
> Now you could get to that if we had support for utility statements
> accepting parameter symbols, ie you could execute
>        DO ... USING $1, $2
> with out-of-line parameter values passed using the PQexecParams protocol.
> So maybe that's an orthogonal feature that should be done as a separate
> patch, but without it I'm not sure there's really much point.

If I remember well, you wrote so this way isn't directly possible. You
have to know a targer datatype - so you have to use syntax DO(target
type list) ... USING ... and there have to be mechanisms to put these
values to PL. Maybe you think to use only varchar variables and then
access to values via array (from PL)?

little bit different question - but I hope related to topic. I
thinking about CALL statement and "true procedures". There are three
request - transaction control, multi record sets, and using IN, OUT
parameters (compatibility issue and conformance with standard). Now I
don't know - CALL statement have to be util statement or classic plan
statement? I inclined to think so util statement can be better. But I
would to use a IN and OUT variables too - so some support for
PQexecParams protocol can be nice

CREATE OR REPLACE PROCEDURE foo(IN a int, IN b int, OUT c int)
...

and using from psql

CALL foo(10,10, :result);
\echo :result

Pavel

>
> IIRC one of the stumbling blocks for parameters in utility statements
> is that usually there's no good context for inferring their data types.
> If we were to extend DO in the particular way Pavel suggests, then
> there would be context for that case, but I'm not sure what we do about
> the general case.  We'd want to think about that before installing a
> special-purpose rule that only works for DO.
>
>                        regards, tom lane
>