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