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