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

Mon, 11 May 1998 14:25:25 +0900
t-ishii@sra.co.jp
functional index