From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jonathan Zacharuk <jonathan.zacharuk@ascentech.ca>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Error when using unquoted identifier that contains the word end in a begin atomic block
Date: Thu, 24 Aug 2023 16:12:26 -0400
Message-ID: <3972353.1692907946@sss.pgh.pa.us> (raw)
In-Reply-To: <YT2PR01MB4318B3BD0B4D84546C3EAC169C1DA@YT2PR01MB4318.CANPRD01.PROD.OUTLOOK.COM>
References: <YT2PR01MB4318B3BD0B4D84546C3EAC169C1DA@YT2PR01MB4318.CANPRD01.PROD.OUTLOOK.COM>
Jonathan Zacharuk <jonathan.zacharuk@ascentech.ca> writes:
> It seems to me that within a BEGIN ATOMIC block the use of the word "end" within an unquoted identifier is causing an error.
I think your problem is somewhere on the client side.
> -- This function is successfully created (note the quoted "effective_end_date").
> CREATE OR REPLACE FUNCTION test_function_success() RETURNS TIMESTAMP
> LANGUAGE SQL
> BEGIN ATOMIC
> SELECT DISTINCT "effective_end_date"
> FROM test_view;
> END
For me, this works (in psql) with or without the double quotes:
regression=# CREATE OR REPLACE FUNCTION test_function_success() RETURNS TIMESTAMP
LANGUAGE SQL
BEGIN ATOMIC
SELECT DISTINCT "effective_end_date"
FROM test_view;
END;
CREATE FUNCTION
regression=# CREATE OR REPLACE FUNCTION test_function_success() RETURNS TIMESTAMP
LANGUAGE SQL
BEGIN ATOMIC
SELECT DISTINCT effective_end_date
FROM test_view;
END;
CREATE FUNCTION
> -- This function fails to be created. I would expect this to work.
> CREATE OR REPLACE FUNCTION test_function_fail() RETURNS TIMESTAMP
> LANGUAGE SQL
> BEGIN ATOMIC
> SELECT DISTINCT effective_end_date
> FROM test_view
> ERROR: syntax error at end of input
> LINE 6: FROM test_view
> ^
I can't help noticing that the trailing END is missing from what you
show here, and it seems pretty clear that the syntax error is about
that, not about the effective_end_date identifier. So the real
question is why the CREATE FUNCTION command is getting sent to the
server before it's complete. If this isn't flat-out pilot error,
then I guess whatever client you are using (apparently not psql) is
getting confused about which occurrence of "end" terminates the
command. You should take this up with the authors of said
client software.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, jonathan.zacharuk@ascentech.ca, pgsql-bugs@lists.postgresql.org
Subject: Re: Error when using unquoted identifier that contains the word end in a begin atomic block
In-Reply-To: <3972353.1692907946@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox