Re: Creating a function index - Mailing list pgsql-cygwin

From Henshall, Stuart - WCP
Subject Re: Creating a function index
Date
Msg-id E2870D8CE1CCD311BAF50008C71EDE8E01F74995@MAIL_EXCHANGE
Whole thread Raw
In response to Creating a function index  ("Michael Labhard" <ince@pacifier.com>)
List pgsql-cygwin

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
>

pgsql-cygwin by date:

Previous
From: "Tom Berger"
Date:
Subject: Problem with install instructions
Next
From: "Henshall, Stuart - WCP"
Date:
Subject: Re: Problem with install instructions