Thread: Creating a function index

Creating a function index

From

"Michael Labhard"

Date:

30 August 2002, 13:45:10

Apparantly creating a function index does not work if there is a single
quote in the function expression?  Example:



CREATE TABLE A (
 x INTEGER
 , y DATE
)
;

INSERT INTO A VALUES( 1, CURRENT_DATE );
INSERT INTO A VALUES( 2, CURRENT_DATE );
INSERT INTO A VALUES( 3, CURRENT_DATE );
INSERT INTO A VALUES( 4, CURRENT_DATE - CAST('1 day' AS INTERVAL) );
--INSERT INTO A VALUES( 4, CURRENT_DATE - CAST('2 day' AS INTERVAL) );
--INSERT INTO A VALUES( 4, CURRENT_DATE - CAST('3 day' AS INTERVAL) );

CREATE INDEX indx1 ON A( DATE_TRUNC('day', y) );

SELECT * FROM A WHERE y < CURRENT_DATE;

DROP INDEX indx1;
DROP TABLE A;


Please advise.  Thanks.

-- Michael

Re: Creating a function index

From

"Henshall, Stuart - WCP"

Date:

30 August 2002, 14:23:39

I was under the impression that you couldn't include
anything but columns in a function call for indexing
so you'd have to do something like the following (untested):
CREATE FUNCTION trunc_to_day(date) RETURNS timestamp AS '
DECLARE
        y ALIAS FOR $1;
        dt timestamp;
BEGIN
        dt=date_trunc('day',y);
        RETURN dt;
END;
' LANGUAGE 'plpgsql' WITH (isstrict,iscachable);

CREATE INDEX indx1 ON A (trunc_to_day(y));

Also I thought dates could dates so a truncation to
day surely wouldn't mean much
hth,
- Stuart

> -----Original Message-----
> From: Michael Labhard [mailto:ince@pacifier.com]
> Sent: 30 August 2002 15:45
> To: pgsql-cygwin@postgresql.org
> Subject: [CYGWIN] Creating a function index
>
>
> Apparantly creating a function index does not work if there
> is a single
> quote in the function expression? Example:
>
>
>
> CREATE TABLE A (
> x INTEGER
> , y DATE
> )
> ;
>
> INSERT INTO A VALUES( 1, CURRENT_DATE );
> INSERT INTO A VALUES( 2, CURRENT_DATE );
> INSERT INTO A VALUES( 3, CURRENT_DATE );
> INSERT INTO A VALUES( 4, CURRENT_DATE - CAST('1 day' AS INTERVAL) );
> --INSERT INTO A VALUES( 4, CURRENT_DATE - CAST('2 day' AS INTERVAL) );
> --INSERT INTO A VALUES( 4, CURRENT_DATE - CAST('3 day' AS INTERVAL) );
>
> CREATE INDEX indx1 ON A( DATE_TRUNC('day', y) );
>
> SELECT * FROM A WHERE y < CURRENT_DATE;
>
> DROP INDEX indx1;
> DROP TABLE A;
>
>
> Please advise. Thanks.
>
> -- Michael
>
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>