Seems like SQL functions cannot be used for defining functional
indexes. Is this a feature or bug? (I couldn't find that restrictions
in docs)
> create table d1 (d datetime);
> insert into d1 values('now'::datetime);
> create index d1index1 on d1 (d);
> create function date2month(datetime) returns datetime as ' select date_trunc(\'month\', datetime($1))' language
'sql';
> create index d1index2 on d1 (date2month(d) datetime_ops);
> ERROR: internal error: untrusted function not supported.
Next, C functions work great for creating functional
indexes. Good. Unfortunately, the functional index I have created
seems never be used. Any suggestion?
create table d1(d date);
CREATE FUNCTION date2month(date)
RETURNS datetime
AS '/mnt2/home/mgr/t-ishii/doc/PostgreSQL/functional_index/date2month/date2month.so'
LANGUAGE 'c';
(300 records insertion here)
create index d1index on d1 using btree (date2month(d) datetime_ops);
vacuum d1;
explain select * from d1 where date2month(d) = 'Mon Mar 01 00:00:00 1999 JST'::datetime;
NOTICE: QUERY PLAN:
Seq Scan on d1 (cost=13.96 size=166 width=4)
EXPLAIN
---------------------- date2month.c --------------------
#include "postgres.h"
#include "utils/builtins.h"
DateTime *date2month(DateADT date)
{
static char *month = "month";
DateTime *d,*ret;
union {
text unit;
char buf[128];
} v;
d = date_datetime(date);
strcpy(VARDATA(&v.unit),month);
VARSIZE(&v.unit) = strlen(month)+VARHDRSZ;
ret = datetime_trunc(&v.unit,d);
return(ret);
}
--
Tatsuo Ishii
t-ishii@sra.co.jp