[SQL] safely exchanging primary keys?

[Louis-David Mitterrand]

Hi,

I have this function which swaps primary keys for cabin_types (so that
id_cabin_type ordering reflects natural data ordering):

        CREATE OR REPLACE FUNCTION swap_cabin_types(id1 integer, id2 integer) 
RETURNS integer
                AS $$
        declare
                tmp integer;
        begin
                tmp := nextval('cabin_type_id_cabin_type_seq');
                update cabin_type set id_cabin_type=tmp where id_cabin_type=id1;
                update cabin_type set id_cabin_type=id1 where id_cabin_type=id2;
                update cabin_type set id_cabin_type=id2 where id_cabin_type=tmp;
                return tmp;
        end;
        $$
        LANGUAGE plpgsql;

'id_cabin_type' is a foreign key for two other tables, 'cabin_category'
and 'alert_cabin_type', which have an "on update cascade" clause.

When I run that function it seems the foreign keys are not properly
updated and the data ends up in a mess.

Did I forget something?

Thanks,

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