Thread: PL/pgSQL THEN binging in condition
postgres=# SELECT version();
version
------------------------------------------------------------
PostgreSQL 16.4, compiled by Visual C++ build 1940, 64-bit
(1 row)
postgres=# DO $$
postgres$# BEGIN
postgres$# IF
postgres$# CASE
postgres$# WHEN TRUE
postgres$# THEN TRUE
postgres$# END
postgres$# THEN
postgres$# NULL;
postgres$# END IF;
postgres$# END
postgres$# $$;
ERROR: syntax error at end of input
LINE 5: WHEN TRUE
^
version
------------------------------------------------------------
PostgreSQL 16.4, compiled by Visual C++ build 1940, 64-bit
(1 row)
postgres=# DO $$
postgres$# BEGIN
postgres$# IF
postgres$# CASE
postgres$# WHEN TRUE
postgres$# THEN TRUE
postgres$# END
postgres$# THEN
postgres$# NULL;
postgres$# END IF;
postgres$# END
postgres$# $$;
ERROR: syntax error at end of input
LINE 5: WHEN TRUE
^
It seems error here because first THEN bound to IF statement rather than CASE expression. Workaround here would be using parenthesis:
postgres=# DO $$
postgres$# BEGIN
postgres$# IF
postgres$# (CASE
postgres$# WHEN TRUE
postgres$# THEN TRUE
postgres$# END)
postgres$# THEN
postgres$# NULL;
postgres$# END IF;
postgres$# END
postgres$# $$;
DO
postgres$# BEGIN
postgres$# IF
postgres$# (CASE
postgres$# WHEN TRUE
postgres$# THEN TRUE
postgres$# END)
postgres$# THEN
postgres$# NULL;
postgres$# END IF;
postgres$# END
postgres$# $$;
DO
If behavior can not be changed for compatibility reasons, it at least can use better error message.
Hi
út 17. 9. 2024 v 20:04 odesílatel PetSerAl <petseral@gmail.com> napsal:
postgres=# SELECT version();
version
------------------------------------------------------------
PostgreSQL 16.4, compiled by Visual C++ build 1940, 64-bit
(1 row)
postgres=# DO $$
postgres$# BEGIN
postgres$# IF
postgres$# CASE
postgres$# WHEN TRUE
postgres$# THEN TRUE
postgres$# END
postgres$# THEN
postgres$# NULL;
postgres$# END IF;
postgres$# END
postgres$# $$;
ERROR: syntax error at end of input
LINE 5: WHEN TRUE
^It seems error here because first THEN bound to IF statement rather than CASE expression. Workaround here would be using parenthesis:postgres=# DO $$
postgres$# BEGIN
postgres$# IF
postgres$# (CASE
postgres$# WHEN TRUE
postgres$# THEN TRUE
postgres$# END)
postgres$# THEN
postgres$# NULL;
postgres$# END IF;
postgres$# END
postgres$# $$;
DOIf behavior can not be changed for compatibility reasons, it at least can use better error message.
It is an interesting use case, but I am afraid it can be fixed (enhanced) without a strong rewrite of the plpgsql engine.
plpgsql parser knows nothing about embedded SQL expressions. It just tries to cut the string between IF and THEN, and in this case, it should be confused.
This is a feature - it is the cost of a very simple plpgsql parser, that doesn't support expressions explicitly.
Regards
Pavel
Pavel Stehule <pavel.stehule@gmail.com> writes:
> út 17. 9. 2024 v 20:04 odesílatel PetSerAl <petseral@gmail.com> napsal:
>> It seems error here because first THEN bound to IF statement rather than
>> CASE expression.
> It is an interesting use case, but I am afraid it can be fixed (enhanced)
> without a strong rewrite of the plpgsql engine.
It would not be that hard to teach read_sql_construct to count nesting
of CASE ... END, as it already does for parentheses. However, I'm a
little worried whether that'd interact badly with plpgsql's own uses
of CASE and END.
regards, tom lane