Thread: Supporting $n parameters in new syntax

Hello hackers,

I'm wrapping up a patch that adds SQL:2011 FOR PORTION OF syntax and
then uses it to implement CASCADE in temporal foreign keys. The FKs
are implemented as triggers, like ordinary FKs, and the trigger
function makes a call through SPI that does `UPDATE %s FOR PORTION OF
%s FROM $%d TO $%d`. But I suspect I'm missing something in the
analyze/rewriting phase, because I get this error:

ERROR:  no value found for parameter 1

That's coming from ExecEvalParamExtern in executor/execExprInterp.c.

If I hardcode some dates instead, the query works (even if I use the
same parameters elsewhere). Does anyone have any hints what I may be
missing? Any suggestions for some other syntax to consult as an
example (e.g. ON CONFLICT DO UPDATE)?

In gram.y I parse the phrase like this:

FOR PORTION OF ColId FROM a_expr TO a_expr

Then in the analysis phase I do this:

    /*
     * Build a range from the FROM ... TO .... bounds.
     * This should give a constant result, so we accept functions like NOW()
     * but not column references, subqueries, etc.
     *
     * It also permits MINVALUE and MAXVALUE like declarative partitions.
     */
    Node *target_start = transformForPortionOfBound(forPortionOf->target_start);
    Node *target_end   = transformForPortionOfBound(forPortionOf->target_end);
    FuncCall *fc = makeFuncCall(SystemFuncName(range_type_name),
                                list_make2(target_start, target_end),
                                COERCE_EXPLICIT_CALL,
                                forPortionOf->range_name_location);
    result->targetRange = transformExpr(pstate, (Node *) fc,
EXPR_KIND_UPDATE_PORTION);

(transformForPortionOfBound just handles MIN/MAXVALUE, and for
targetRange I feed the bounds into a range type constructor to use
later.)

I was hoping that transformExpr would do everything I need re
identifying parameters, but maybe there is something else in a later
phase?

Thanks,
Paul

On Tue, May 18, 2021 at 3:00 PM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:
>
> I suspect I'm missing something in the
> analyze/rewriting phase, because I get this error:
>
> ERROR:  no value found for parameter 1
> . . .
>
> I was hoping that transformExpr would do everything I need re
> identifying parameters, but maybe there is something else in a later
> phase?

Never mind, I think I figured it out. The problem was that I was
calling ExecEvalExpr with CreateStandaloneExprContext(), and I should
have been using the context from the query.

Thanks!
Paul