Thread

1. date bug

   Jeff Patterson <jpat@mywayhealth.com> — 2001-05-15T01:33:32Z

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

2. Re: date bug

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-16T22:59:40Z

   "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