Thread: date bug

Strange date behavior as shown below.

create table "holidays" ("date" date NOT NULL,"name" varchar(25));
insert into holidays values ('01-01-2001'::date,'New Years');
insert into holidays values ('01-15-2001'::date,'Kings Birthday');
insert into holidays values ('02-19-2001'::date,'Presidents Day');

create function is_holiday (date) returns bool as
'select
    case
        when $1=date then 1::bool
        else 0::bool
    end
from holidays' language 'sql'

test=# select date,is_holiday(date) from holidays;

        date   | is_holiday
-----------------+------------
 2001-01-01 | t
 2001-01-15 | f
 2001-02-19 | f
(3 rows)




-------------------------------------------
Jeffery S. Patterson
Meta-Lynx

e-mail: jpat@meta-lynx.com
Phone : 707-431-9320
  Fax : 707-433-2918

Meta-Lynx
132 Mill St. Suite 210
Healdsburg, CA 95448



=======================================================================
This message contains information that may be confidential and
privileged.  Unless you are the addressee (or authorized to receive for
the addressee), you may not use, copy or disclose to anyone the message
or any information contained in the message.  If you have received the
message in error, please advise the sender by reply e-mail
jpat@meta-lynx.com, and delete the message.  Thank you very much.
=======================================================================

"Jeff Patterson" <jpat@mywayhealth.com> writes:
> Strange date behavior as shown below.

This is not a date bug; it's your misunderstanding of how SQL functions
work.  A function that returns a scalar can only return one result, so
it makes no sense to try to define its result as a SELECT from a
multi-row table.

As it happens, what you get is the result from the first row returned
by the SELECT.  (Personally I'd have thought it'd make more sense to
raise an error if the SELECT returns multiple rows, but we'd probably
break existing applications if we changed it.)

Perhaps you want something on the order of

    SELECT EXISTS(SELECT 1 FROM holidays WHERE date = $1)

            regards, tom lane