Surprisingly forgiving behavior when a case expression is terminated with "end case"
От | Bryn Llewellyn |
---|---|
Тема | Surprisingly forgiving behavior when a case expression is terminated with "end case" |
Дата | |
Msg-id | 5C1A009F-1E4A-4C2B-9142-9B05F2A86225@yugabyte.com обсуждение исходный текст |
Ответы |
Re: Surprisingly forgiving behavior when a case expression is terminated with "end case"
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: Surprisingly forgiving behavior when a case expression is terminated with "end case" (Adrian Klaver <adrian.klaver@aklaver.com>) |
Список | pgsql-general |
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?
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.
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.
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;
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;
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;
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
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.
В списке pgsql-general по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: Surprisingly forgiving behavior when a case expression is terminated with "end case"