Обсуждение: plpgsql's case bug?
Hi,
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
ismissing 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>
simplycontains a <case statement else
clause>, then let SL be the <SQL statement list> of that <case statement else clause>. c) Otherwise, an exception
conditionis raised: case not found for
case statement, and the execution of the <case statement> is terminated immediately.
"""
comments?
--
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: > 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 > ...Robert > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >