pg.ddx.io pgsql-bugs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Error when using unquoted identifier that contains the word end in a begin atomic block 3+ messages / 2 participants [nested] [flat]
* Error when using unquoted identifier that contains the word end in a begin atomic block @ 2023-08-24 19:53 Jonathan Zacharuk <jonathan.zacharuk@ascentech.ca> 0 siblings, 1 reply; 3+ messages in thread From: Jonathan Zacharuk @ 2023-08-24 19:53 UTC (permalink / raw) To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org> PostgreSQL 15.2 (Debian 15.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit Run via Docker 15.4 image It seems to me that within a BEGIN ATOMIC block the use of the word "end" within an unquoted identifier is causing an error. The word "end" is a reserved keyword. However, it is my understanding from the documentation that keywords cannot be used *as* an identifier, but they should be able to be used within an identifier. For instance, the "end" in effective_end_date is a valid identifier. -- Create a data set with the word end in it. CREATE VIEW test_view AS SELECT CURRENT_TIMESTAMP as effective_end_date > OK > Query Time: 0.002s -- 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 > Affected rows: 0 > Query Time: 0.001s -- 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 ^ > Query Time: 0s ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Error when using unquoted identifier that contains the word end in a begin atomic block @ 2023-08-24 20:12 Tom Lane <tgl@sss.pgh.pa.us> parent: Jonathan Zacharuk <jonathan.zacharuk@ascentech.ca> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2023-08-24 20:12 UTC (permalink / raw) To: Jonathan Zacharuk <jonathan.zacharuk@ascentech.ca>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: Error when using unquoted identifier that contains the word end in a begin atomic block @ 2023-08-24 20:23 Jonathan Zacharuk <jonathan.zacharuk@ascentech.ca> parent: Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Jonathan Zacharuk @ 2023-08-24 20:23 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org> Thanks Tom. I should have noticed that the command I was running was not completely getting sent to the server. This does indeed look like a Navicat issue. I have confirmed with psql that this works fine and will ensure to do so in the future! -----Original Message----- From: Tom Lane <tgl@sss.pgh.pa.us> Sent: Thursday, August 24, 2023 1:12 PM To: Jonathan Zacharuk <jonathan.zacharuk@ascentech.ca> Cc: pgsql-bugs@lists.postgresql.org Subject: Re: Error when using unquoted identifier that contains the word end in a begin atomic block 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-08-24 20:23 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-08-24 19:53 Error when using unquoted identifier that contains the word end in a begin atomic block Jonathan Zacharuk <jonathan.zacharuk@ascentech.ca> 2023-08-24 20:12 ` Tom Lane <tgl@sss.pgh.pa.us> 2023-08-24 20:23 ` Jonathan Zacharuk <jonathan.zacharuk@ascentech.ca>
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