Thread: inferred param types for PREPARE

Attached is a patch that makes the specification of parameters to the
PREPARE SQL statement more flexible: the user can specify "unknown" to
have the type of a particular parameter inferred from the context in
which the parameter is used (if possible). If fewer parameter types are
specified than the number of parameters used in the query, the remaining
parameter types will be inferred in the same way. This is analogous to
the way that parameters can be specified and inferred for protocol-level
prepared statements.

The only trickiness was allowing "UNKNOWN" to be specified in the list
of type names in the PREPARE statement. Since UNKNOWN was previously an
unreserved_keyword, this caused reduce/reduce conflicts:

state 1186

  788 prep_type_elem: UNKNOWN .
  1477 unreserved_keyword: UNKNOWN .

    ')'       reduce using rule 788 (prep_type_elem)
    ')'       [reduce using rule 1477 (unreserved_keyword)]
    ','       reduce using rule 788 (prep_type_elem)
    ','       [reduce using rule 1477 (unreserved_keyword)]
    $default  reduce using rule 1477 (unreserved_keyword)

I promoted UNKNOWN to be a col_name_keyword (like most of the other
builtin type names), which solved the problem. If there is a better fix,
let me know (I won't claim to be a yacc expert).

This is just a quick and dirty patch; if no one objects to this feature,
tomorrow I will post a revised patch that includes updates to the
documentation and regression tests.

-Neil

Neil Conway <neilc@samurai.com> writes:
> The only trickiness was allowing "UNKNOWN" to be specified in the list
> of type names in the PREPARE statement. Since UNKNOWN was previously an
> unreserved_keyword, this caused reduce/reduce conflicts:

You're doing it wrong.  There is no need for any special case whatever
in gram.y --- ordinary lookup of the type name will do fine.

            regards, tom lane

On Sun, 2006-01-15 at 12:17 -0500, Tom Lane wrote:
> You're doing it wrong.  There is no need for any special case whatever
> in gram.y --- ordinary lookup of the type name will do fine.

Woops, good point. Attached is a revised patch that doesn't modify the
grammar, and includes updates to the documentation and regression tests.
Applied to HEAD.

-Neil

Neil Conway <neilc@samurai.com> writes:
> Woops, good point. Attached is a revised patch that doesn't modify the
> grammar, and includes updates to the documentation and regression tests.

BTW, I wonder whether it wouldn't be a better idea to declare the
pg_prepared_statement view's parameter_types column as regtype[]
instead of oid[].  This would make the default output much more
readable, ie you'd get things like '{integer,boolean}' instead of
'{23,16}'.  Of course you can cast in either direction to get the
other form, but defaulting to text instead of numeric output seems
like it might be a better choice.

The option of casting to get the other form should probably be mentioned
in the view's documentation page, whichever is the default.

            regards, tom lane

On Sun, 2006-01-15 at 17:33 -0500, Tom Lane wrote:
> BTW, I wonder whether it wouldn't be a better idea to declare the
> pg_prepared_statement view's parameter_types column as regtype[]
> instead of oid[].

Yeah, good point -- I had thought that using type names would be
ambiguous in the presence of schemas, but of course regtype solves that
problem. Attached is a patch that implements this -- barring any
objections I'll apply this tomorrow.

-Neil