Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 81E2B476F7A for ; Wed, 15 Jan 2003 07:03:23 -0500 (EST) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 145AC475D64 for ; Wed, 15 Jan 2003 07:01:58 -0500 (EST) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50]) by matrix.gatewaynet.com (8.12.5/8.12.5) with ESMTP id h0FGCFnv002533; Wed, 15 Jan 2003 14:12:15 -0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.5/8.12.5/Submit) with ESMTP id h0FGCFhh002529; Wed, 15 Jan 2003 14:12:15 -0200 Date: Wed, 15 Jan 2003 14:12:15 -0200 (GMT+2) From: Achilleus Mantzios To: Andreas Joseph Krogh Cc: pgsql-sql@postgresql.org Subject: Re: index on to_char(created, 'YYYY') doesn't work In-Reply-To: <200301151152.05952.andreak@officenet.no> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/209 X-Sequence-Number: 11074 On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Wednesday 15 January 2003 11:37, you wrote: > > The following does not work: > > > > create index session_u_idx on session (to_char(created, 'YYYY')); > > ERROR: parser: parse error at or near "'YYYY'" at character 57 > > > > Can I make a function to do this and index using the result of that > > funtion? Do anyone have an example of such a function? > > I tried the following function: > - ----------------------------------------------------------------- > create function drus (timestamp) returns varchar AS' > DECLARE > str_created VARCHAR; > created ALIAS FOR $1; > BEGIN > str_created:= to_char(created, ''YYYY''); > RETURN str_created; > END; > ' LANGUAGE 'plpgsql'; add WITH (iscachable) > > create index session_u_idx on session (drus(created)); > - ----------------------------------------------------------------- > But it failes with: > ERROR: DefineIndex: index function must be marked isImmutable > > Now the question is how do I mark an index function isImmutable? > > - -- > Andreas Joseph Krogh > There will always be someone who agrees with you > but is, inexplicably, a moron. > > gpg public_key: http://dev.officenet.no/~andreak/public_key.asc > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.0.7 (GNU/Linux) > > iD8DBQE+JUtlUopImDh2gfQRAl1XAKCkEDKxRDys/Di1gLLRDx6h0TGiPwCeI4FN > DNdajyaQTd27f8MeaWZ+xUE= > =T3we > -----END PGP SIGNATURE----- > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr