On 2024-04-06 20:14 +0200, Michal Bartak wrote:
> The issue described bellow exists in postgresql ver 16.2 (found in some
> previous major versions)
Can confirm also on master.
> The documentation defines a comment as:
>
> > A comment is a sequence of characters beginning with double dashes and
> > extending to the end of the line
>
>
> When using such a comment within CASE control block, it ends up with an
> error:
>
> DO LANGUAGE plpgsql $$
> DECLARE
> t TEXT = 'a';
> BEGIN
> CASE t
> WHEN 'a' -- my comment
> THEN RAISE NOTICE 'a';
> WHEN 'b'
> THEN RAISE NOTICE 'b';
> ELSE NULL;
> END CASE;
> END;$$;
>
> ERROR: syntax error at end of input
> LINE 1: "__Case__Variable_2__" IN ('a' -- my comment)
> ^
> QUERY: "__Case__Variable_2__" IN ('a' -- my comment)
> CONTEXT: PL/pgSQL function inline_code_block line 5 at CASE
I'm surprised that the comment is not skipped by the scanner at this
point. Maybe because the parser just reads the raw expression between
WHEN and THEN with plpgsql_append_source_text via read_sql_construct.
How about the attached patch? It's a workaround by simply adding a line
feed character between the raw expression and the closing parenthesis.
--
Erik
>From 85456a22f41a8a51703650f2853fb6d8c9711fc7 Mon Sep 17 00:00:00 2001
From: Erik Wienhold <[email protected]>
Date: Sat, 6 Apr 2024 22:36:54 +0200
Subject: [PATCH v1] plpgsql: create valid IN expression for CASE WHEN clause
The expression in CASE x WHEN <expr> THEN may end with a line comment.
This results in a syntax error when the WHEN clause is rewritten to
x IN (<expr>). Cope with that by appending \n after <expr> to terminate
the line comment.
---
src/pl/plpgsql/src/expected/plpgsql_control.out | 17 +++++++++++++++++
src/pl/plpgsql/src/pl_gram.y | 6 +++++-
src/pl/plpgsql/src/sql/plpgsql_control.sql | 14 ++++++++++++++
3 files changed, 36 insertions(+), 1 deletion(-)
diff --git a/src/pl/plpgsql/src/expected/plpgsql_control.out
b/src/pl/plpgsql/src/expected/plpgsql_control.out
index 328bd48586..ccd4f54704 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_control.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_control.out
@@ -681,3 +681,20 @@ select case_test(13);
other
(1 row)
+-- test line comment between WHEN and THEN
+create or replace function case_comment(int) returns text as $$
+begin
+ case $1
+ when 1 -- comment before THEN
+ then return 'one';
+ else
+ return 'other';
+ end case;
+end;
+$$ language plpgsql immutable;
+select case_comment(1);
+ case_comment
+--------------
+ one
+(1 row)
+
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index bef33d58a2..98339589ba 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -4161,7 +4161,11 @@ make_case(int location, PLpgSQL_expr *t_expr,
/* Do the string hacking */
initStringInfo(&ds);
- appendStringInfo(&ds, "\"%s\" IN (%s)",
+ /*
+ * The read expression may end in a line comment, so
+ * append \n after it to create a valid expression.
+ */
+ appendStringInfo(&ds, "\"%s\" IN (%s\n)",
varname, expr->query);
pfree(expr->query);
diff --git a/src/pl/plpgsql/src/sql/plpgsql_control.sql
b/src/pl/plpgsql/src/sql/plpgsql_control.sql
index ed7231134f..8e007c51dc 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_control.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_control.sql
@@ -486,3 +486,17 @@ select case_test(1);
select case_test(2);
select case_test(12);
select case_test(13);
+
+-- test line comment between WHEN and THEN
+create or replace function case_comment(int) returns text as $$
+begin
+ case $1
+ when 1 -- comment before THEN
+ then return 'one';
+ else
+ return 'other';
+ end case;
+end;
+$$ language plpgsql immutable;
+
+select case_comment(1);
--
2.44.0
