Re: SPI/backend equivalent of extended-query Describe(statement)?

On 05/26/18 10:03, Tom Lane wrote:
> Really our hook mechanism only supports adding hooks, not removing them.

I suppose the pllua_spi_prepare_checkparam_hook could be linked in once
and for all, and turned on and off just where the code now hooks and
unhooks it, and just forward to the next hook when it's off.

>> Yeah. Another issue I ran into is that if you use SPI_prepare_params,
>> then you have to use SPI_execute_plan_with_paramlist, it's not possible
>> to use SPI_execute_plan (or SPI_execute_snapshot) instead because
>> plan->nargs was set to 0 by the prepare and never filled in with the
>> actual parameter count.
>
> I'm not following why that's such a problem? The whole point of
> SPI_prepare_params and friends is that the actual number and types
> of the parameters is hidden behind the parse hooks and ParamListInfo
> --- and, indeed, could change from one execution to the next.
> SPI_execute_plan only makes sense with a predetermined, fixed
> parameter list.

Well, when you're implementing a PL, you're faced with this task of
mapping/coercing parameters from a PL type system that invariably
(invariably? Yes, I think, unless your PL's name has "sql" in it)
differs from SQL's, and that's a pretty ill-defined task if your PL
runtime is handed a query to prepare in the form of a string, and
then handed some parameter values in the PL's type system, and can't
find out what SQL types they could appropriately be mapped/coerced to
for the query to be valid.

In the JDBC API, you pass a query string to prepareStatement(), and
then on what you get back you can call getParameterMetaData() and
learn what SQL thinks the types of the parameters will have to be.
That isn't really expected to change; the requirement isn't necessarily
to support some dizzying all-dynamic-all-the-time usage pattern, it's
just to be able to get the information, and SPI_prepare_params seems
the only way to get it.

-Chap