I need to write a function which inserts a log entry in a log table and only
keeps the last 30 records. I was thinking of using a subquery similar to the
following:
insert into log (account_id, message) values (1, 'this is a test);
delete from log where account_id = 1 and id not in ( select id from log
where account_id = 1 order by timestamp desc limit 30);
I'm wondering if there is a more performance oriented method of doing the
delete that I'm not thinking of.
Just for the sake of alternatives -
create sequence cy30 maxvalue 30 cycle;
insert into log values(select generate_series(1,30), 'dummy');
INSERT 0 30
update log set des='....' where account_id=(select nextval('cy30'));
UPDATE 1
There are details to consider I guess. For example what if an update fails
and the sequence already advanced... Also, since we cycle the id, for
sorting, we'll need to add timestamp or something similar.
My 2 pence...
P.S.
This A) failed me and I wonder if this is supposed to be so or if it's
just a place where no one treaded on ??
B) works fine except it doesn't advance the sequence.
A) update tc set des='b' where id=nextval('cy30')::int;
UPDATE 30
B) update tc set des='c' where id=currval('cy30');
UPDATE 1
Regards,
Ben K.
Developer
http://benix.tamu.edu
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
