Re: [SQL] plpgsql doesn't coerce boolean expressions to boolean

Jan Wieck <JanWieck(at)Yahoo(dot)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