Re: Row pattern recognition

Hi Tatsuo, Jian,

DEFINE rejects a qualified column reference, citing ISO/IEC 19075-5
6.5, and the message reads as though the clause enforces that
absolutely:

ERROR: qualified expression "g1.thr" is not allowed in DEFINE clause
HINT: Write the name without its qualifier, or write "(x).field" to
select a field of a composite value.

In PL/pgSQL, one pragma walks through it, and it reaches further than
I expected -- as far as the one spelling DEFINE reserves for a
pattern variable. I went looking for a way to close that and came
away thinking we should not. Below is what gets through, and why I
would leave it.

Setup:

CREATE TABLE t (i int, price int);
INSERT INTO t SELECT g, g*10 FROM generate_series(1, 10) g;
CREATE TYPE lim AS (thr int);

Rejected, as expected:

CREATE FUNCTION g1(thr int) RETURNS bigint AS $$
DECLARE r bigint;
BEGIN
SELECT count(*) OVER (ORDER BY i
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (a+) DEFINE a AS price < g1.thr)
INTO r FROM t LIMIT 1;
RETURN r;
END$$ LANGUAGE plpgsql;

SELECT g1(55);
ERROR: qualified expression "g1.thr" is not allowed in DEFINE clause

Add one line and the same function runs:

CREATE FUNCTION g2(thr int) RETURNS bigint AS $$
#variable_conflict use_variable
DECLARE r bigint;
BEGIN
... DEFINE a AS price < g2.thr ...
END$$ LANGUAGE plpgsql;

SELECT g2(55); -- no error

A record variable's field goes through the same way, and so does a
whole-row reference:

DECLARE r bigint; rec lim;
... rec.thr := 55; ...
... DEFINE a AS price < rec.thr ... -- no error
... DEFINE a AS rec.* IS NOT NULL ... -- no error

Then there is this one. Give the record the name of a pattern
variable:

CREATE FUNCTION g5() RETURNS bigint AS $$
#variable_conflict use_variable
DECLARE r bigint; a lim;
BEGIN
a.thr := 55;
SELECT count(*) OVER (ORDER BY i
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (a+) DEFINE a AS price < a.thr)
INTO r FROM t LIMIT 1;
RETURN r;
END$$ LANGUAGE plpgsql;

SELECT g5(); -- no error; a.thr reads the record

Drop the pragma from that same function and it is:

ERROR: pattern variable qualified expression "a.thr" is not
supported in DEFINE clause

So "a.thr" -- the one spelling DEFINE reserves for a pattern variable
-- quietly means a PL/pgSQL record instead, and nothing says so.

How it gets past:

transformColumnRef() calls p_pre_columnref_hook first and returns
with whatever it answers:

if (pstate->p_pre_columnref_hook != NULL)
{
node = pstate->p_pre_columnref_hook(pstate, cref);
if (node != NULL)
return node;
}

PL/pgSQL installs plpgsql_pre_column_ref(), which answers only under
"#variable_conflict use_variable" and returns NULL otherwise. That
is why the default mode still rejects: there the name is resolved by
the post hook, and that result does reach DEFINE's checks.

DEFINE has three of them and all three are below that return. Two
are after core resolution and have to be -- they ask what the
qualifier turned out to name. The first asks nothing. It is the
block that rejects a pattern variable qualifier and a whole-row
reference, and its own comment says why it needs no resolution:

* A whole-row reference is barred by its form alone: no qualifier
* makes one legal here, so resolving it first would only choose which
* rejection it gets.

That is the block g5 walks past.

Why not put a check above the hook:

This is the hook doing its job. The obvious move -- lift the
form-based block above the pre hook call, since it asks nothing about
meaning -- does not hold up: "rec.*" and "a.thr" are PL/pgSQL's own
spellings, so reading the form already picks SQL's reading of text
belonging to the other scope. A check above the hook does not avoid
the disagreement, it settles it without asking. The switch at the
top of transformColumnRef() is no precedent for doing so: where it
bars column references outright -- a DEFAULT expression, a partition
bound -- no name resolution is on offer at all, so there is no scope
to defer to.

Nor is 6.5 being bent. What it reserves is the qualifier slot of an
SQL name: in DEFINE that qualifier is a row pattern variable, which
is what rules out a range variable there. A PL/pgSQL routine or
record name is neither, and the standard says nothing about it. So
rejecting "g1.thr" is broader than 6.5 asks for -- our own choice,
and a conservative one. It is easier to let an extension override a
rule we chose, in names it owns, than one the standard imposes.

The pattern variable case is not a new concession either.
"#variable_conflict use_variable" already outranks a table column,
and a pattern variable name is another name on the SQL side;
outranking it is the same rule applied once more. The user wrote the
pragma to say so.

So I would document it rather than close it: the restriction is a
rule about how DEFINE resolves names, not a guarantee about how they
may be spelled.

Does that read right to you?

Best regards,
Henson