funcional index PostgreSQL

[m-dance]

I've tried to create functional index like
that:
=== cut ===
db=> CREATE INDEX NDX_FNC_TBL_TRAFBYTES__DATE ON tbl_trafbytes (date (date_trunc ('day', trafbytes_date)));
ERROR: parser: parse error at or near "("
=== cut ===

Column trafbytes_date define as
=== cut ===
trafbytes_date | timestamp without time zone | not null
=== cut ===

Does it means that PostgreSQL funcional indexes support only simplest fucntions like lower ()? :-F

[Anon]

You cannot nest functions as a functional index.

Use a custom function as your functional index.

[Anon]

From the postgresql todo list:

• Allow CREATE INDEX zman_index ON test (date_trunc( 'day', zman ) datetime_ops)
  fails index can't store constant parameters

So that's your first problem.

What you can probably do is create your own function sort of like this:

CREATE FUNCTION myfunc(date) RETURNS integer AS'
date (date_trunc ('day', trafbytes_date)
' LANGUAGE 'SQL' WITH iscachable, isstrict;

CREATE INDEX myindex on tbl_trafbytes(myfunc(trafbytes_date));

NOTE: THIS IS PSEUDO CODE - IT WON'T WORK EXACTLY AS-IS.

Chris