| 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: | Whole Thread | Raw Message | 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:
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/
| 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 |