Modified FIFO queue and insert rule

From: "Leif B(dot) Kristensen" <leif(at)solumslekt(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Modified FIFO queue and insert rule
Date: 2007-08-08 12:18:42
Message-ID: 200708081418.42503.leif@solumslekt.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2007-08-08 12:24:35 Re: Reordering columns, will this ever be simple?
Previous Message André Volpato 2007-08-08 11:56:47 Data Mart with Postgres