Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.2/8.9.1) with ESMTP id LAA61062; Tue, 30 Mar 1999 11:33:16 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id LAA06915; Tue, 30 Mar 1999 11:31:35 -0500 (EST) To: Thomas Lockhart cc: "Jackson, DeJuan" , Andrew Merrill , pgsql-sql@hub.org, PGSQL Hackers Subject: Re: [SQL] indexing a datetime by date In-reply-to: Your message of Tue, 30 Mar 1999 06:14:13 +0000 <37006BB5.E5720D9@alumni.caltech.edu> Date: Tue, 30 Mar 1999 11:31:35 -0500 Message-ID: <6913.922811495@sss.pgh.pa.us> From: Tom Lane Thomas Lockhart writes: >> create index when_ndx3 on notes (date(when) date_ops); >> Which won't work because the date(datetime) function isn't trusted. > Tom, does this ring a bell with you? No, and in fact datetime_date *is* marked trusted in pg_proc, both current sources and 6.4.2. I see the problem DeJuan is getting at: play=> create table notes (when datetime); CREATE play=> create index when_ndx3 on notes (date(when) date_ops); CREATE play=> insert into notes values ('now'); ERROR: internal error: untrusted function not supported. This is either a bug or a very poorly worded error message. I'll look into it. In the meantime, a workaround is to call the function using its builtin name: play=> create table notes (when datetime); CREATE play=> create index when_ndx3 on notes (datetime_date(when) date_ops); CREATE play=> insert into notes values ('now'); INSERT 1086489 1 In 6.4.2, date() on a datetime is an SQL-language function that just calls the builtin function datetime_date(). It would seem that 6.4.2 can't cope with an SQL-language function as an index generator. This might be a minor bug or it might be difficult to change; I dunno. In 6.5, date() on a datetime is a true builtin, on par with datetime_date(), so you'll be able to use either name interchangeably in that release. But we may still not be able to do anything with other SQL-language functions as index generators. regards, tom lane