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 <e...@ewie.name> 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