I found an excellent description of how to implement a fifo que in
PostgreSQL at Greg Mullane's blog:
http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html
I have used the 'rule' approach to implement a queue that generates a
quick-list of last selected places. The only modification I need is
that if an item already exists in the list, a new reference should be
written to the top, and the old reference should be deleted. But it
seems like I'm in over my head here:
-- short FIFO list of recently selected places
CREATE TABLE recent_places (
id SERIAL PRIMARY KEY,
place_fk INTEGER REFERENCES places ON DELETE CASCADE
);
CREATE RULE placelimit AS
ON INSERT TO recent_places DO ALSO
DELETE FROM recent_places
WHERE
-- this clause doesn't work
-- (place_fk = NEW.place_fk AND id <> NEW.id) OR
id NOT IN (SELECT id FROM recent_places ORDER BY id DESC LIMIT 10);
When I try to use the commented clause above, no records are written to
the table at all! Why?
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/
