Sanding down some edge cases for PL/pgSQL reserved words

This is a rather delayed response to the discussion of bug
#18693 [1], in which I wrote:

> (It's kind of annoying that "strict" has to be double-quoted
> in the RAISE NOTICE, especially since you get a rather misleading
> error if it isn't. But that seems like a different discussion.)

As an example of that, if you don't double-quote "strict"
in this usage you get

regression=# do $$ declare r record; begin
SELECT a, b AS STRICT INTO r FROM (SELECT 'A' AS a, 'B' AS b) AS q;
RAISE NOTICE 'STRICT r.strict = %', r.strict;
end $$;
ERROR: record "r" has no field "strict"
LINE 1: r.strict
^
QUERY: r.strict
CONTEXT: PL/pgSQL function inline_code_block line 3 at RAISE

which is pretty bogus because the record *does* have a field
named "strict". The actual problem is that STRICT is a fully
reserved PL/pgSQL keyword, which means you need to double-quote
it if you want to use it this way.

The attached patches provide two independent responses to that:

1. AFAICS, there is no real reason for STRICT to be a reserved
rather than unreserved PL/pgSQL keyword, and for that matter not
EXECUTE either. Making them unreserved does allow some ambiguity,
but I don't think there's any surprises in how that ambiguity
would be resolved; and certainly we've preferred ambiguity over
introducing new reserved keywords in PL/pgSQL before. I think
these two just escaped that treatment by dint of being ancient.

2. That "has no field" error message is flat-out wrong. The now-known
way to trigger it has a different cause, and what's more, we simply do
not know at this point whether the malleable record type has such a
field. So in 0002 below I just changed it to assume that the problem
is a reserved field name. We might find another way to reach that
failure in future, but I doubt that "has no field" would be the right
thing to say in any case.

This is v19 material at this point, so I'll stick it on the CF queue.

regards, tom lane

[1] https://www.postgresql.org/message-id/flat/18693-65968418890877b4%40postgresql.org

On Sat, Apr 26, 2025, at 06:44, Tom Lane wrote:
> This is a rather delayed response to the discussion of bug
> #18693 [1], in which I wrote:
...
> which is pretty bogus because the record *does* have a field
> named "strict". The actual problem is that STRICT is a fully
> reserved PL/pgSQL keyword, which means you need to double-quote
> it if you want to use it this way.

I'd like to briefly raise an old nostalgic PL/pgSQL dream of mine that might be
affected by this change.

For years, I've felt we could benefit from introducing convenience syntax to
explicitly require that exactly one row is affected by a query, something which
currently requires using a somewhat cumbersome workaround:

- Using `... INTO STRICT ...` for `SELECT`,
- Using `RETURNING ... INTO STRICT ...` for `DELETE/UPDATE/INSERT`, or
- Checking `ROW_COUNT` via `GET DIAGNOSTICS` and raising an error if not exactly one row.

I think it would be more convenient and intuitive if we could simply write:

```
STRICT [SELECT | UPDATE | INSERT | DELETE] ...;
```

That is, allowing `STRICT` followed directly by any regular `SELECT`, `UPDATE`,
`INSERT`, or `DELETE` command, explicitly enforcing exactly one affected row.

Changing `STRICT` to become an unreserved keyword in PL/pgSQL would effectively
close the window of opportunity for this syntax, as it would introduce ambiguity
in command parsing.

I was actually not aware of STRICT already being a reserved PL/pgSQL keyword.
Had I known that, I would have proposed this convenience syntax already since
a long time ago.

I wonder how often developers truly need to use "strict" as a field name versus
the potential usage of a clean and explicit syntax for enforcing single-row
results without additional verbosity.

/Joel

"Joel Jacobson" <joel(at)compiler(dot)org> writes:
> For years, I've felt we could benefit from introducing convenience syntax to
> explicitly require that exactly one row is affected by a query, something which
> currently requires using a somewhat cumbersome workaround:

> - Using `... INTO STRICT ...` for `SELECT`,
> - Using `RETURNING ... INTO STRICT ...` for `DELETE/UPDATE/INSERT`, or
> - Checking `ROW_COUNT` via `GET DIAGNOSTICS` and raising an error if not exactly one row.

> I think it would be more convenient and intuitive if we could simply write:

> ```
> STRICT [SELECT | UPDATE | INSERT | DELETE] ...;
> ```

Meh. I don't really have an opinion on whether this is worth bespoke
syntax, but if it is:

(1) I don't see why we'd restrict it to plpgsql as opposed to
implementing it in core SQL.

(2) Putting the keyword at the front seems fairly un-SQL-like.
For SELECT, "SELECT STRICT ..." would seem more natural, as it calls
back to SELECT DISTINCT; or you could imagine integrating it into the
LIMIT clause. Not as sure what to do for the DML commands, but
somewhere near where we put RETURNING seems saner.

Also, even if we did do it in plpgsql exactly as you suggest, making
it unreserved doesn't move the needle on whether that's possible.
Most of plpgsql's statement-starting keywords are unreserved.

But please, don't hijack this thread for that discussion ...

regards, tom lane

On Sat, Apr 26, 2025, at 05:10, Tom Lane wrote:
> "Joel Jacobson" <joel(at)compiler(dot)org> writes:
>> For years, I've felt we could benefit from introducing convenience syntax to
>> explicitly require that exactly one row is affected by a query, something which
>> currently requires using a somewhat cumbersome workaround:
>
>> - Using `... INTO STRICT ...` for `SELECT`,
>> - Using `RETURNING ... INTO STRICT ...` for `DELETE/UPDATE/INSERT`, or
>> - Checking `ROW_COUNT` via `GET DIAGNOSTICS` and raising an error if not exactly one row.
>
>> I think it would be more convenient and intuitive if we could simply write:
>
>> ```
>> STRICT [SELECT | UPDATE | INSERT | DELETE] ...;
>> ```
>
> Meh. I don't really have an opinion on whether this is worth bespoke
> syntax, but if it is:
>
> (1) I don't see why we'd restrict it to plpgsql as opposed to
> implementing it in core SQL.

Good point, I agree, that would be much better.

>
> (2) Putting the keyword at the front seems fairly un-SQL-like.
> For SELECT, "SELECT STRICT ..." would seem more natural, as it calls
> back to SELECT DISTINCT; or you could imagine integrating it into the
> LIMIT clause. Not as sure what to do for the DML commands, but
> somewhere near where we put RETURNING seems saner.
>
> Also, even if we did do it in plpgsql exactly as you suggest, making
> it unreserved doesn't move the needle on whether that's possible.
> Most of plpgsql's statement-starting keywords are unreserved.
>
> But please, don't hijack this thread for that discussion ...

Understood, and thanks for clarifying this change doesn't affect the strictness idea.

/Joel