plpgsql's case bug?
in [PgSql]
|
Prev: pgsql: Augment WAL records for btree deletewith GetOldestXmin() to
Next: [HACKERS] Alpha release this week?
From: Jaime Casanova on 27 Mar 2010 22:14 On Sat, Mar 27, 2010 at 10:08 PM, Jaime Casanova <jcasanov(a)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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 27 Mar 2010 23:44 On Sat, Mar 27, 2010 at 10:08 PM, Jaime Casanova <jcasanov(a)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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 28 Mar 2010 00:38 Robert Haas <robertmhaas(a)gmail.com> writes: > On Sat, Mar 27, 2010 at 10:08 PM, Jaime Casanova > <jcasanov(a)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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jaime Casanova on 28 Mar 2010 01:17 On Sun, Mar 28, 2010 at 12:38 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas(a)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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 28 Mar 2010 01:21 Jaime Casanova <jcasanov(a)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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
|
Next
|
Last
Pages: 1 2 Prev: pgsql: Augment WAL records for btree deletewith GetOldestXmin() to Next: [HACKERS] Alpha release this week? |