Re: plpgsql CASE statement - last version

Pavel Stehule wrote:
> Hello
>
> I found some bugs when I used base_lexer, so I returned back own
> lexer. It's only little bit longer, but simpler.

Hmm. I don't like having to lex the expressions again. It just doesn't
feel right.

How about taking a completely different strategy, and implement the
CASE-WHEN construct fully natively in plpgsql, instead of trying to
convert it to a single SQL CASE-WHEN expression? It's not a very good
match anyway; you have to do tricks to convert the comma-separated lists
of WHEN expressions to WHEN-THEN clauses, and you can't use the
THEN-clauses as is, but you have to replace them with offsets into the
array. I think implementing the logic in pl_exec.c would lead to cleaner
code.

FWIW, the current approach gives pretty cryptic CONTEXT information in
error messages as well. For example, this pretty simple case-when example:

postgres=# create or replace FUNCTION case_test(int)
returns text as $$
begin
case $1
when 1 then
return 'one';
when 'invalid' then
return 'two';
when 3,4,5 then
return 'three, four or five';
end case;
end;
$$ language plpgsql immutable;
CREATE FUNCTION

gives this pretty hard-to-understand error message:

postgres=# SELECT case_test(1);
ERROR: invalid input syntax for integer: "invalid"
CONTEXT: SQL statement "SELECT CASE $1 WHEN 1 THEN 1 WHEN
'invalid' THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 3 WHEN 5 THEN 3 END "
PL/pgSQL function "case_test" line 2 at unknown

BTW, what does PL/SQL or PSM say about the type-compatibility of the
CASE and the WHENs? We're very lenient in assignments, how should this
behave?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com