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

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> Tom Lane wrote:
>> 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?

> 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.

Sure, it was easy given a certain implementation technique. Question
is, do we want to consider it a supported feature even if it makes the
implementation hard?

> In the same way you can do
> varname := count(*) from Room where roomno = new.roomno;

This actually doesn't bother me; I see it as simply a variant syntax
for SELECT INTO. (Perhaps it should be documented that way.)

If we want to preserve this behavior for IF et al, I don't think there
is any practical way to apply SQL-level type coercion as I had wanted.
We could instead make the code act like it's assigning to a plpgsql
boolean variable --- but it will apply plpgsql's textual conversion
methods, not SQL type coercion.

regards, tom lane