Add a serial column to a table based on a sort clause

[Igor Katson <descentspb@xxxxxxxxx>]

I have a table, which has a creation_ts (timestamp) column, but does not
have a id (serial) column. I want to add such a one, but, AFAIK, if I enter

ALTER TABLE table ADD COLUMN id serial

it will randomly put the sequence numbers.

I wrote a function, which uses a cursor and UPDATE WHERE CURRENT OF, and
it works, but is there any other, more "elegant", way?

CREATE OR REPLACE FUNCTION newsfeed_seq_add_cursor
() RETURNS void AS $$
    DECLARE
        curs refcursor;
        rec record;
    BEGIN
        create sequence seq;
        ALTER TABLE table ADD COLUMN id int;
        OPEN curs FOR SELECT * FROM table ORDER BY creation_ts FOR UPDATE;
        FETCH curs INTO rec;
        WHILE FOUND IS TRUE
        LOOP
            UPDATE table SET id = nextval('seq') WHERE CURRENT OF curs;
        END LOOP;
        ALTER TABLE table ALTER COLUMN id SET NOT NULL, ALTER COLUMN id
SET DEFAULT nextval('seq');
    END;
$$ language plpgsql;


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general