boolean function return values

From: Eugene Barlow <barlow(at)tripadvisor(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: boolean function return values
Date: 2004-08-11 17:41:28
Message-ID: 1092246088.28529.8.camel@legolas
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Can someone explain why I would get different results when using "WHERE
x()" v.s. using "WHERE x() = true" for functions that return a
boolean? We have also seen the query optimizer behave differently
between the two uses when using functional indices.

I am using postgres 7.4.2 and here are the real details:

CREATE OR REPLACE FUNCTION validlivesitecontent(text, text, integer)
RETURNS boolean
AS '
DECLARE
Publisher ALIAS for $1;
ContentType ALIAS for $2;
Status ALIAS for $3;
BEGIN
return (Status & 3 = 0) and (Publisher != ''DataSource'' and
ContentType != ''Packages'' and ContentType != ''SpecialDeals'');
END;
'
LANGUAGE plpgsql IMMUTABLE STRICT;

user1=# select count(*) from t_content where
validlivesitecontent(publisher, contenttype, status);
count
--------
770403
(1 row)

user1=# select count(*) from t_content where
validlivesitecontent(publisher, contenttype, status) = true;
count
---------
1258365
(1 row)

Thanks!
--Eugene Barlow
TripAdvisor - www.tripadvisor.com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Treat 2004-08-11 17:51:49 Re: PostgreSQL 8.0 Feature List?
Previous Message Tom Lane 2004-08-11 16:28:51 Re: psql: immediately exit after an error?