From: | "culley harrelson" <culley(at)ml1(dot)net> |
---|---|
To: | "pg-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | resequencing a user defined integer column |
Date: | 2003-05-08 19:03:38 |
Message-ID: | 20030508190338.118CD5BA47@smtp.us2.messagingengine.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am trying to come up with a PL/pgSQL algorithm to resequence the
item_order column in this table:
/////////////////////////////////////////////////////////
DROP SEQUENCE document_item_seq;
CREATE SEQUENCE document_item_seq;
DROP TABLE document_item CASCADE;
CREATE TABLE document_item(
document_item_id INTEGER NOT NULL DEFAULT nextval('document_item_seq'),
document_id INTEGER NOT NULL,
item_text TEXT NOT NULL,
item_order INTEGER
);
/////////////////////////////////////////////////////////
based on the document_id column. Say we insert this data:
insert into document_item(document_id, item_text, item_order) values (1,
'text 1', 1);
insert into document_item(document_id, item_text, item_order) values (1,
'text 2', 2);
insert into document_item(document_id, item_text, item_order) values (1,
'text 3', 3);
insert into document_item(document_id, item_text, item_order) values (1,
'text 4', 4);
insert into document_item(document_id, item_text, item_order) values (1,
'text 5', 2);
On the 5th insert I want a trigger to re-oder 2, 3, and 4 to be 3, 4 and
5. I need item_order to be unique and sequential for any given
document_id. Does anyone have a similar piece of pl/pgsql they could
share? In the past I have added a timestamp column that tracked the
modification time of the record and added it to the sort order (select *
from document_item order by item_order asc, mod_time desc) but I know
there has to be a better way...
culley
From | Date | Subject | |
---|---|---|---|
Next Message | Dennis Gearon | 2003-05-08 19:10:08 | Re: resequencing a user defined integer column |
Previous Message | Jon Earle | 2003-05-08 18:50:30 | Error installing postgresql-7.3.2 |