resequencing a user defined integer column
От | culley harrelson |
---|---|
Тема | resequencing a user defined integer column |
Дата | |
Msg-id | 20030508190338.118CD5BA47@smtp.us2.messagingengine.com обсуждение исходный текст |
Ответы |
Re: resequencing a user defined integer column
|
Список | 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
В списке pgsql-general по дате отправления: