On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Wednesday 15 January 2003 16:12, you wrote: > > 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) > > Thank you, not _that_ works:-) > But now this doesn't work: > create index session_u_idx on session (drus(created), username);
Functinal indexes are single column indexes. Why dont you change your function to: create function drus (timestamp,varchar) returns varchar A and return the concatenation of to_char(created, ''YYYY'')||$2 and then create the index as usual (passing the date and the username as params to your function)???? > > Can't I have multicolumn-indexes with functions? Any idea how to rewrite that > so it works? > Here is my session table: > CREATE TABLE session ( > session_id varchar(256) NOT NULL PRIMARY KEY, > created timestamp DEFAULT 'now' NOT NULL, > last_accessed timestamp NOT NULL, > destroyed timestamp NOT NULL, > username varchar -- Allow sessions from not logged in users > ); > > Here is my query I wish to optimize using indexes: > SELECT to_char(created, 'IW') as week, count(session_id) from session WHERE > username IS NULL and to_char(created, 'YYYY') = '2002' group by week ORDER BY > week; > > Any hints on optimizing this query, index-usage etc? > > - -- > Andreas Joseph Krogh <[EMAIL PROTECTED]> > 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+JU8hUopImDh2gfQRAuA5AKCXyqCZk92d6oCgyJ/Auf8c4xkSaQCgr4Lq > /+r2WSydbYWXNomMvbmt2E8= > =N6NQ > -----END PGP SIGNATURE----- > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ================================================================== 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: [EMAIL PROTECTED] [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
