Mail-Followup-To: pgsql-sql@postgresql.org
To: pgsql-sql@postgresql.org
connect(): No such file or directory
From: Tim Landscheidt <tim@tim-landscheidt.de>
Subject: Re: safely exchanging primary keys?
Date: Mon, 24 May 2010 14:38:39 +0000
Organization: <URI:http://www.tim-landscheidt.de/>
Lines: 41
Message-ID: <m3d3wl1i1c.fsf@passepartout.tim-landscheidt.de>
References: <20100524085101.GA21249@apartia.fr>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: g224121215.adsl.alicedsl.de
Mail-Copies-To: never
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:OH+IP+T1ops3IL1U3nV1MD0K65U=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/107
X-Sequence-Number: 34155

Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote:

> 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?

What does "are not properly updated" mean? Anyhow, why don't
you use something simple like (untested):

| UPDATE cabin_type
|   SET id_cabin_type =
|     CASE
|       WHEN id_cabin_type = id1 THEN
|         id2
|       ELSE
|         id1
|     END
|   WHERE id_cabin_type IN (id1, id2);

Tim