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
>