The account of the CASE expression here:
https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE
says that it's terminated with the keyword END (just as I've always
understood)—i.e. not with the PL/pgSQL CASE statement's END CASE.
Moreover CASE is a reserved word—as a "create table case(…)" attempt shows. Yet
CASE is tolerated (using PG 14.4) here:
select 1 as case;
In fact, any reserved word that I try (like IF, THEN, and so on) is accepted as
an alias. This seems to me to be wrong. What do you (all) think?
This outcome inspired this test:
create table t(k serial primary key, v text);
insert into t(v) values ('a'), (null);
select
k,
case
when v is null then '<NULL>'
else v
end case
from t order by k;
I suppose that this is showing nothing more than what I already did. Everything
that I've shown so far behaves the same if PG 11.
So then I tried the "typo" in a PL/pgSQL subprogram:
create function f(arr in text[])
returns text
language plpgsql
as $body$
declare
a text;
r text := '';
begin
foreach a in array arr loop
a := case
when a is null then '<NULL>'
else a
end case;
r := r||a||', ';
end loop;
return r;
end;
$body$;
select f(array['a', null::text, 'b']);
The "create function" succeeds. And the "select f()" executes without error to
produce the result that I expect. In PG 14.4.
But in PG 11.9, the "create function" causes this error:
ERROR: syntax error at or near "case"
LINE 13: end case;
It seems, then, that at some version boundary between PG 11 and PG 14,
forgiveness was introduced in this secnario, too.
Was this change to forgive what seems to be to be a straight syntax error
deliberate? After all, you (all) thought it to be a syntax error in some
scenarios in PG 11—but just not so in all scenarios.
Was it that the original sin of forgiveness in some scenarios could not be
corrected because of the stronger requirement not to break existing code? And
so this led to a "bug" fix to forgive that sin more uniformly? If so, then I
suppose that you might say something in the doc. But there is still a wrinkle.
This:
select
k,
case
when v is null then '<NULL>'
else v
end dog
from t order by k;
runs without error. But this (in PG 14.4)
select
k,
case
when v is null then '<NULL>'
else v
end case dog
from t order by k;
still fails with a syntax error:
ERROR: syntax error at or near "dog"
LINE 6: end case dog
So even in "current", the "end case" type isn't forgiven in all scenarios.
p.s. You can guess that I stumbled on this in the context of a fairly large
demo app where just one of the subprograms had the "end case" typo that I
showed above. Nothing prompted me to spot my mistake until I tested my code
using YugabyteDB. I'm embarrassed to say that our current version still uses
the PG 11 SQL processing code. But a soon-to-be-published new YB version will
use PG 13. And "soon" after that, we hope to remain current with the current PG.
