Re: resequencing a user defined integer column

Add another column and resequence that.

culley harrelson wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>