From: | Tomek Zielonka <tomek-lists(at)mult(dot)i(dot)pl> |
---|---|
To: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | functional indices with functions taking row as argument |
Date: | 2001-03-02 00:38:09 |
Message-ID: | 20010302013809.A11880@mult.i.pl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi.
I have a table which represents clients' sessions / orders.
CREATE TABLE sessions (
id INTEGER NOT NULL DEFAULT nextval('sessions_seq'),
...
finished TIMESTAMP,
bl_confirmed BOOL NOT NULL DEFAULT false,
bl_sent BOOL NOT NULL DEFAULT false,
bl_paid BOOL NOT NULL DEFAULT false,
bl_ok BOOL NOT NULL DEFAULT false,
PRIMARY KEY (id)
);
bl_* fields define state of the session. I define some conditions based on
those booleans and other fields. Example:
CREATE FUNCTION is_session_to_be_sent (SESSIONS) RETURNS BOOL AS '
SELECT ($1.bl_ok) AND ($1.bl_confirmed) AND (NOT $1.bl_sent)
' LANGUAGE 'sql';
I don't want to explicitly pass those three arguments to the function, because
there may be more of them in the future. So I pass SESSIONS type arg.
It would be nice, if I could create functional index on this table. Then the
following query might be faster.
SELECT * FROM sessions s WHERE is_session_to_be_sent(s) = true;
Unfortunately, it seems that there is no appropriate syntax for creating such
an index.
Hope I'm wrong ;)
(I see a workaround - additional boolean field updated in triggers)
greetings,
Tom
--
.signature: Too many levels of symbolic links
From | Date | Subject | |
---|---|---|---|
Next Message | David Lynn | 2001-03-02 00:48:43 | Perl & DBI/DBD::Pg confusion with finish |
Previous Message | Jonny Deng | 2001-03-01 23:23:52 | Want to learn the Postgresql database system |