From: Jonathan Zacharuk <jonathan.zacharuk@ascentech.ca>
To: Tom Lane <tgl@sss.pgh.pa.us>
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
Thread-Topic: Error when using unquoted identifier that contains the word end
 in a begin atomic block
Thread-Index: AdnWxHDKIppFRA9nQPO79TBS4BBUrAAAtwIAAABG5jA=
Date: Thu, 24 Aug 2023 20:23:00 +0000
Message-ID: 
 <YT2PR01MB431868625CDBA6086F22A0359C1DA@YT2PR01MB4318.CANPRD01.PROD.OUTLOOK.COM>
References: 
 <YT2PR01MB4318B3BD0B4D84546C3EAC169C1DA@YT2PR01MB4318.CANPRD01.PROD.OUTLOOK.COM>
 <3972353.1692907946@sss.pgh.pa.us>
In-Reply-To: <3972353.1692907946@sss.pgh.pa.us>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?k31jJWl6B5zais2/0Tc5BKY7M7euavMe5E2R/zjEcGcQV9zrZsfFZCJGYJ25?=
 =?us-ascii?Q?gJVCay8J5SYxe1UQCYA3MSRCTOVyuxFnGvpB2I/0XtX2ZWGcXZcmhrHKkNzQ?=
 =?us-ascii?Q?Jrh4TF4YBGch+L2EUfktRsEXMOsJbsbDypyhU7ozGCo+uMjptKoprWpQb/+k?=
 =?us-ascii?Q?P8BWNJ4Gz1Z1rMPe/7lVORTleI1Hbp9yCmaUG+lbByPBLd93D8xxajl1/zZ9?=
 =?us-ascii?Q?j1q0vxICimRaoIwvgrLl0T+MDL91qt9OLY0BsVbVnEKi/8bYUzaDTxir6E+5?=
 =?us-ascii?Q?+K9vwIdYzI9ZP6bdlxsRWLq41hfFZGpX3waZCd+7vOz2CnoN8HuyXQwgK57s?=
 =?us-ascii?Q?ldGOBvT+N9ST9MkHJ2UBTAIuHf83S42z3OYsxj6DucV6Q7cqlBJd3oz6YllC?=
 =?us-ascii?Q?CPjKTKcVsnPardJ4G/6qfeQeePTCpAyQSt69m27EYOj0lbDUmfMRPatp+VJY?=
 =?us-ascii?Q?lYh4PIx9g6uf31sL2vqdYXNR6eSDCM9a92sDBoRnIeSbmaIHHpVBx3Yr5/JQ?=
 =?us-ascii?Q?ik2Hg2J3b/O8pr12J4sIXArFDUjP40Ea6wjO4EroW4tKfIyPlqRpGsflblkJ?=
 =?us-ascii?Q?JYWkl+KoJFik5unAUQEYPlTXp84P4vKEGIYUGmd8YvHjirSaFX65i4NaCUbj?=
 =?us-ascii?Q?un2hQaGBAmcZWxdlE5CdGjcRCqWyl57IQr+2Nps8AGZHPrGw7RxbgqO7ocwW?=
 =?us-ascii?Q?jezsyJVYsAaRNJIByOR0HAD2ffLYYNd84QXahSIJknrJgmPq3zxtwwVK4+oo?=
 =?us-ascii?Q?2v9tbHSmB6kjO2sfMmee45MeJ/0cztETxq3MpTDpWkVdJ9+8IxCu0uxJzYqN?=
 =?us-ascii?Q?ym+0elqr+8r6vPQoF+3W27Mr8wexhAVQi2VjiWd2vdlvoXogyqa7+wAGKXOV?=
 =?us-ascii?Q?FYKqy9OnRBhP6Qh/m2kj2xRYMho5jxb/wQlCW7msrRXSmH0zGQvudb3YMSLM?=
 =?us-ascii?Q?OudWhOvJeq5GrLsz5E6cg772+gBCTq/vYwIqll62lv43OJnY1M5jB/5dUknp?=
 =?us-ascii?Q?VHvTGMDeJAYDnmbuW2s2SdY5gezZh94qZateByt1+cWbAsfySslR+Z7sPITB?=
 =?us-ascii?Q?Fra2brrKO+43EW0T7ONXgfOqhKxMB8oJ2EJq7siqMJKbVrBp4fbKyvf0AsNg?=
 =?us-ascii?Q?sTvKAyTNtga0lZXzT/qN/EBf2gJ5gIxwEx5XDzvJiNql+OwGxolhNICpS8hk?=
 =?us-ascii?Q?HZqM6/Jl4bW9fQAe8FEkpE8+YxjzAVcbgTuzWnae+fEG5g6HHrWAVmU+DUyX?=
 =?us-ascii?Q?HyDmkzhkoE0velkBVyIQrMjVBGinbeeOq3MdlInrTalVthGGgifW6n5M2FN6?=
 =?us-ascii?Q?SL7pPyOXiLTx40Ymi2Ak5L70nRWHSOx6EHvI1MTGzxL5VIVevluFHESYcdSr?=
 =?us-ascii?Q?6QuJ5onCn+yLkqi5zAIl4czrGnvMPegg8jsi+DLPl36GGrWwVRnItGE/ufPd?=
 =?us-ascii?Q?OPaTH9Vhp5YNCCoUXEoPMnZmqCYC/YhXtH9qGLwT6DAht4QjkMmEkmfYXlTb?=
 =?us-ascii?Q?lBqKLsDy+8T03FUiMsq7HZwNhBk6pSSIHUwQwIl1fZ4FKq7KrFmM1BCR9/kN?=
 =?us-ascii?Q?CbqAHajlamW538LkVZKOlxOoO55PACWclPejkys6DpgeZ43Iar4yb8BdLDeh?=
 =?us-ascii?Q?0Q=3D=3D?=
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: ascentech.ca
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: YT2PR01MB4318.CANPRD01.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 9fbe2957-5afa-43f2-7dda-08dba4dfe954
X-MS-Exchange-CrossTenant-originalarrivaltime: 24 Aug 2023 20:23:00.2339
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: fc2627e6-54d9-4d18-8ad4-f9ce7ae64cab
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 qKQA6Ft9+6U0nVQ6xmfSfh/7VhBYZKsh7aG+Eh3xNgrbikR+XBVqwlL5H7N6iCGYm7+mubIMdF1VXAs6JUy0avmmbdM1N62Sqf4GkNiI1ow=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: YQBPR01MB10829
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/YT2PR01MB431868625CDBA6086F22A0359C1DA%40YT2PR01MB4318.CANPRD01.PROD.OUTLOOK.COM>
Precedence: bulk

Thanks Tom. I should have noticed that the command I was running was not co=
mpletely getting sent to the server. This does indeed look like a Navicat i=
ssue. 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>=20
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 en=
d 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=20
> 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=3D# CREATE OR REPLACE FUNCTION test_function_success() RETURNS T=
IMESTAMP LANGUAGE SQL BEGIN ATOMIC
  SELECT DISTINCT "effective_end_date"
                FROM test_view;
END;
CREATE FUNCTION
regression=3D# CREATE OR REPLACE FUNCTION test_function_success() RETURNS T=
IMESTAMP 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=20
> 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 h=
ere, and it seems pretty clear that the syntax error is about that, not abo=
ut the effective_end_date identifier.  So the real question is why the CREA=
TE 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 usin=
g (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