Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)

On Thursday, Oct 23, 2003, at 02:44 Asia/Tokyo, Tom Lane wrote:

> Michael Glaesmann <grzm(at)myrealbox(dot)com> writes:
>> CREATE INDEX dborders_extract_month_idx ON dborders (EXTRACT(MONTH
>> from
>> date));
>> ERROR: parser: parse error at or near "(" at character 61
>
> You can't do that in pre-7.4 releases; the syntax of a functional index
> can only be "ON table (func(col1,col2,...))" --- that is, a simple,
> standard-notation function applied to one or more columns of the table.
> So to do this, you'd need to create an intermediate function along
> the lines of "month_trunc(date)"; and you'd have to use it in your
> queries as well as in the index definition.

On Thursday, Oct 23, 2003, at 02:53 Asia/Tokyo, Josh Berkus wrote:
> Oh, sorry. There's an implementation issue with funcional indexes,
> where they
> can't take parameters other than column names. So you need to do:
>
> CREATE FUNCTION get_month (
> TIMESTAMPTZ ) RETURNS INTEGER AS
> ' SELECT EXTRACT(MONTH from $1); '
> LANGUAGE sql IMMUTABLE STRICT;

Thanks, Tom and Josh! Added a type cast of the extract (which returns a
double precision) and it's all good.

Tom commented:

> 7.4 is more flexible though --- it will take the above as long as you
> put an extra set of parentheses in there...

I took a gander at the documentation for 7.4beta. I can tell it's been
reorganized. There's no longer a specific section on functional indexes
that I can see, though there is mention of it in the SQL CREATE INDEX
entry
<http://developer.postgresql.org/docs/postgres/sql-createindex.html>
The 7.3.2 documents I have say that there cannot be multicolumn
functional indexes, though there's no mention of this in the new
documentation. Does this mean this proscription has been lifted?

Thanks again for your help!

Michael