Jan Wieck <[EMAIL PROTECTED]> 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 have to admit it was less an intention than more a side effect of the actual implementation. It was so easy to simply stick "SELECT " in front of "everything between IF and THEN" and expect the result to be a boolean.
In the same way you can do
varname := count(*) from Room where roomno = new.roomno;
which is straight forward because it's simply sticking "SELECT " in front of "everything between := and ;". Well, this does a bit more in that it tries the typinput(typoutput(result)) casting hack ... I know that you don't like that one.
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.
Without parsing much, much more, and finally parsing basically the whole SQL grammar in the PL/pgSQL parser, I don't see how you can do that.
Jan
-- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
