From: | Louis-David Mitterrand <vindex+lists-pgsql-sql(at)apartia(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | safely exchanging primary keys? |
Date: | 2010-05-24 08:51:09 |
Message-ID: | 20100524085101.GA21249@apartia.fr |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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,
From | Date | Subject | |
---|---|---|---|
Next Message | jr | 2010-05-24 11:46:55 | Re: safely exchanging primary keys? |
Previous Message | jr | 2010-05-24 01:15:25 | Re: return %ROWTYPE from function |