Date: Mon, 24 May 2010 10:51:09 +0200
From: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
To: pgsql-sql@postgresql.org
Subject: safely exchanging primary keys?
Message-ID: <20100524085101.GA21249@apartia.fr>
Mail-Followup-To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.20 (2009-06-14)

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,