plpgsql's case bug?

On Sat, Mar 27, 2010 at 10:08 PM, Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:

Hi,

[...]

this is based on General Rules case 1 of chapter 13.6 (case statement)
but i don't think that behaviour is the meaning of that Rule. Even if
it is, the exception should be: "case not found for case statement"
and not just "case not found"

ah! and this is in the PSM spec BTW

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

On Sat, Mar 27, 2010 at 10:08 PM, Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:

I found something strange (at least for my limited view).

in pl_exec.c:1549 in the function exec_stmt_case() we have this:

"""
   /* SQL2003 mandates this error if there was no ELSE clause */
   if (!stmt->have_else)
       ereport(ERROR,
               (errcode(ERRCODE_CASE_NOT_FOUND),
                errmsg("case not found"),
                errhint("CASE statement is missing ELSE part.")));
"""

resulting in this function to fail when i try to execute it

"""
postgres=# create or replace function test_case() returns boolean as $$
declare
       v_one integer = 1;
       v_two boolean = false;
begin
       case when v_one is null then
      v_two = true;
       end case;
       return v_two;
end
$$ language plpgsql;
CREATE FUNCTION

postgres=# select test_case();
ERROR:  case not found
HINT:  CASE statement is missing ELSE part.
CONTEXT:  PL/pgSQL function "test_case" line 5 at CASE
"""

this is based on General Rules case 1 of chapter 13.6 (case statement)
but i don't think that behaviour is the meaning of that Rule. Even if
it is, the exception should be: "case not found for case statement"
and not just "case not found"

"""
General Rules
1) Case:
  a) If the <search condition> of some <searched case statement when
clause> in a <case statement> is
     True, then let SL be the <SQL statement list> of the first
(leftmost) <searched case statement when
     clause> whose <search condition> is True.
  b) If the <case statement> simply contains a <case statement else
clause>, then let SL be the <SQL statement
     list> of that <case statement else clause>.
  c) Otherwise, an exception condition is raised: case not found for
case statement, and the execution of
     the <case statement> is terminated immediately.
"""

comments?

It seems odd to require a dummy ELSE clause that does nothing just to
avoid having an exception thrown, but I'm not sure what else to make
of the quoted portion of the spec. What do you think it's saying?

...Robert

Robert Haas <robertmhaas@gmail.com> writes:

On Sat, Mar 27, 2010 at 10:08 PM, Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:

this is based on General Rules case 1 of chapter 13.6 (case statement)
but i don't think that behaviour is the meaning of that Rule. Even if
it is, the exception should be: "case not found for case statement"
and not just "case not found"

General Rules
1) Case:
� a) If the <search condition> of some <searched case statement when
clause> in a <case statement> is
� � �True, then let SL be the <SQL statement list> of the first
(leftmost) <searched case statement when
� � �clause> whose <search condition> is True.
� b) If the <case statement> simply contains a <case statement else
clause>, then let SL be the <SQL statement
� � �list> of that <case statement else clause>.
� c) Otherwise, an exception condition is raised: case not found for
case statement, and the execution of
� � �the <case statement> is terminated immediately.

It seems odd to require a dummy ELSE clause that does nothing just to
avoid having an exception thrown, but I'm not sure what else to make
of the quoted portion of the spec. What do you think it's saying?

I concur that we seem to be implementing the behavior the spec requires.

As for the spelling of the error message, I don't believe that the spec
intends to mandate any particular spelling of the text, only the value
of the SQLSTATE code. Were this not so, any translation of error
messages would be a spec violation all by itself.

regards, tom lane

On Sun, Mar 28, 2010 at 12:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

It seems odd to require a dummy ELSE clause that does nothing just to
avoid having an exception thrown, but I'm not sure what else to make
of the quoted portion of the spec.  What do you think it's saying?

I concur that we seem to be implementing the behavior the spec requires.

ok. it's seems it means what we have... remember english is not my
mother tongue ;)
and Oracle seems to be doing the same:
http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/x/Case/start.htm

As for the spelling of the error message, I don't believe that the spec
intends to mandate any particular spelling of the text, only the value
of the SQLSTATE code.  Were this not so, any translation of error
messages would be a spec violation all by itself.

well actually i get here when translating the plpgsql messages and
found very strange a message like "case not found", and as a last
argument (a weak one, i have to admit) i will say that the hint could
be innecesary if we use the same message the spec is suggesting... but
i won't die for this, so...

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova <jcasanov@systemguards.com.ec> writes:

well actually i get here when translating the plpgsql messages and
found very strange a message like "case not found", and as a last
argument (a weak one, i have to admit) i will say that the hint could
be innecesary if we use the same message the spec is suggesting... but
i won't die for this, so...

I don't think so ... the spec's wording doesn't seem to me to suggest at
all that you should insert an ELSE clause. It just looks redundant :-(

regards, tom lane

It seems odd to require a dummy ELSE clause that does nothing just to
avoid having an exception thrown, but I'm not sure what else to make
of the quoted portion of the spec.  What do you think it's saying?

I thinking so it designed to be protected before processing CASE
statement without any effect. You can design CASE for some values, and
when is there different value (in runtime), then your an design can be
invalid. So current the design is need some lines more, but is safe.

SQL CASE returns NULL if no expression is executed. PSM CASE raises exception.

Regards
Pavel