Horton

Thread

Commits

  1. Fix plpgsql to allow new-style SQL CREATE FUNCTION as a SQL command.

  1. Parsing error with begin atomic syntax used in a do block

    Samuel Dussault <samuel.dussault@usherbrooke.ca> — 2024-01-17T21:18:17Z

    Hi,

I encounter a problem when creating a procedure using the begin atomic syntax in a script encapsulated in a DO block. When executing the DO block, the parser throws a syntax error. I would expect to be able to create the procedure with both the new begin atomic and the old syntax.

The attached script, demo.sql, highlights the problem and expected behaviours. First, it prints the PostgreSQL version, creates an example table and sets the verbosity parameter to verbose. Then, it successfully creates a procedure using the new begin atomic syntax. Afterwards, it successfully creates the same function using the old syntax but encapsulates the instruction in a do block. Finally, it fails to create the same function using the new syntax with the do block.

The attached file demo.out shows the output I get when executing the script using psql (version: psql (PostgreSQL) 16.0):
PGPASSWORD=postgres psql -h localhost -U postgres -p 15432 -d postgres -f demo.sql &> demo.out

Thank you for your time,
Samuel Dussault

  2. Re: Parsing error with begin atomic syntax used in a do block

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-17T23:17:27Z

    Samuel Dussault <Samuel.Dussault@USherbrooke.ca> writes:
> I encounter a problem when creating a procedure using the begin atomic syntax in a script encapsulated in a DO block. When executing the DO block, the parser throws a syntax error. I would expect to be able to create the procedure with both the new begin atomic and the old syntax.

> do
> $$
>     begin
>         create or replace procedure test2()
>             language sql
>         begin atomic
>             insert into example (id) values (1);
>         end;
>     exception
>         when others then
>             rollback;
>     end
> $$;

The problem here is that "begin" is a reserved word in plpgsql
(and for that matter, so is "end").

I'm not terribly excited about trying to find some kludge that
would make this work, because it would almost certainly break
other cases.  I'd suggest doing the "create procedure" step
via EXECUTE, so that you can hide "begin ... end" from the
plpgsql parser in a string literal.

			regards, tom lane

  3. Re: Parsing error with begin atomic syntax used in a do block

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-18T02:30:07Z

    I wrote:
> The problem here is that "begin" is a reserved word in plpgsql
> (and for that matter, so is "end").

Oh!  I'm mistaken, that is not the issue.  The problem is that
plpgsql assumes that the SQL command ends at the first semicolon.

psql has pretty much this same issue of "does this semicolon end
the command", and it has some heuristics for dealing with that.
Since that's survived awhile now without complaints, we could do
worse than to propagate the identical heuristics into plpgsql,
more or less as attached.

(This also fixes the same problem for multi-command CREATE RULE,
which has been broken in plpgsql since the late bronze age;
but there were not enough people complaining to prompt a fix.)

			regards, tom lane