From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jan Wieck <JanWieck@Yahoo.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [SQL] plpgsql doesn't coerce boolean expressions to boolean
Date: Sat, 27 Sep 2003 21:28:33 -0400
Message-ID: <3487.1064712513@sss.pgh.pa.us> (raw)
In-Reply-To: <3F5E84CF.6090706@Yahoo.com>
References: <15403.1063035632@sss.pgh.pa.us>
<3F5E0A5D.2010803@Yahoo.com>
<3559.1063128729@sss.pgh.pa.us>
<3F5E84CF.6090706@Yahoo.com>
Jan Wieck <JanWieck@Yahoo.com> writes:
> Tom Lane wrote:
>>> 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans
>>> will be accepted in exactly the same cases where they'd be accepted
>>> in a boolean-requiring SQL construct (such as CASE). (By default,
>>> none are, so this isn't really different from #2. But people could
>>> create casts to boolean to override this behavior in a controlled
>>> fashion.)
> Agreed - #4.
My first attempt at doing this failed to pass the regression tests,
because it wasn't prepared for this:
if count(*) = 0 from Room where roomno = new.roomno then
raise exception ''Room % does not exist'', new.roomno;
end if;
Is this really intended to be a feature? It manages to work because
plpgsql simply sticks "SELECT " in front of whatever appears between
IF and THEN, and passes the result to the main SQL engine. But it sure
surprised the heck out of me. The documentation gives no hint that
you're allowed to write anything but a straight boolean expression in IF.
Does Oracle allow that sort of thing?
I would be inclined to think that a more reasonable expression of the
intent would be
if (select count(*) from Room where roomno = new.roomno) = 0 then
Certainly we'd have a big problem supporting the existing coding if we
ever reimplement plpgsql with more awareness of what expressions are.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, JanWieck@Yahoo.com, pgsql-hackers@postgresql.org
Subject: Re: [SQL] plpgsql doesn't coerce boolean expressions to boolean
In-Reply-To: <3487.1064712513@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox