indexing date_part
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!
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
Show quoted text
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
72So 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