Thread: indexing date_part

In this case you just have a syntax error in your function. You have
tried to use a single-quoted string inside of a single-quoted string.
Escape the single quotes by writing "''" instead of "'" inside the
function definition.

For example:

CREATE FUNCTION month_idxable(date) returns date AS 'SELECT
date_part(''month'', date) from mchistw' LANGUAGE 'SQL' WITH
(iscachable);

After creating the function, just create the functional index and you
should be on the right track.

Regards,
    Jeff Davis


On Mon, 2005-02-28 at 14:35 -0500, DEV wrote:
> Hello all,
>
>
>
> I am interested in setting up an index with a date_part as part of it…
> From what I have been able to find I need to write a function that
> will return the date_part that I want I have tried
> CREATE FUNCTION month_idxable(date) returns date AS 'SELECT
> date_part('month', date) from mchistw' LANGUAGE 'SQL' WITH
> (iscachable);
>
> And I get: ERROR:  parser: parse error at or near "month" at character
> 72
>
>
>
> So what am I doing wrong and how would I include the function into an
> index?
>
>
>
> Thank you all for all of your help!
>
>

Jeff Davis <jdavis-pgsql@empires.org> writes:
> In this case you just have a syntax error in your function. You have
> tried to use a single-quoted string inside of a single-quoted string.
> Escape the single quotes by writing "''" instead of "'" inside the
> function definition.

BTW, if you were working in a more recent version of Postgres, you
wouldn't need the wrapper function in the first place.  This works
fine in 7.4 and later:

regression=# create table foo(f1 date);
CREATE TABLE
regression=# create index fooi on foo(date_part('month', f1));
CREATE INDEX
regression=#

            regards, tom lane