[SQL] safely exchanging primary keys?
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
Re: [SQL] safely exchanging primary keys?
On Mon, May 24, 2010 at 10:51:01AM +0200, Louis-David Mitterrand wrote: > Hi, > > I have this function which swaps primary keys for cabin_types (so that > id_cabin_type ordering reflects natural data ordering): Actually this function works fine. My problem was elsewhere. Sorry for barking up the wrong tree. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] safely exchanging primary keys?
And relying on keys for a sort order is a very wrong tree :) On 05/24/2010 08:05 AM, Louis-David Mitterrand wrote: > On Mon, May 24, 2010 at 10:51:01AM +0200, Louis-David Mitterrand wrote: >> Hi, >> >> I have this function which swaps primary keys for cabin_types (so that >> id_cabin_type ordering reflects natural data ordering): > > Actually this function works fine. My problem was elsewhere. Sorry for > barking up the wrong tree. > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] safely exchanging primary keys?
Louis-David Mitterrand writes: > When I run that function it seems the foreign keys are not properly > updated and the data ends up in a mess. Yeah? Could we see an actual example of what you're talking about? And which PG version is this? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] safely exchanging primary keys?
Louis-David Mitterrand 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] safely exchanging primary keys?
On Mon, May 24, 2010 at 02:38:39PM +, Tim Landscheidt wrote: > Louis-David Mitterrand wrote: > > What does "are not properly updated" mean? Anyhow, why don't Hi, I did follow-up on my own post: the problem was elsewhere. > 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); Nice, thanks. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] safely exchanging primary keys?
On Mon, May 24, 2010 at 07:00:30PM +0200, Louis-David Mitterrand wrote: > On Mon, May 24, 2010 at 02:38:39PM +, Tim Landscheidt wrote: > > 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); > > Nice, thanks. Ah, but this won't work as the UNIQUE PK constraint is in force. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] User function that returns a set of rows.
Hi, I'm struggling to write what seed at the time a simple function like: "CREATE FUNCTION foo() RETURNS IDONO as $$ select * from tbl; return (whatever that will give the *); $$ up till now I got this far: CREATE OR REPLACE FUNCTION select_business_types () RETURNS SETOF RECORD AS $body$ DECLARE rec RECORD; -- fields business_type_id business_type_name BEGIN FOR rec IN SELECT * FROM sv_bo_business_types LOOP RETURN NEXT rec; END LOOP; RETURN; END; $body$ LANGUAGE 'plpgsql'; When I tried it from the shell I got a nasty error message about that I am not in an environment to receive a set ??? (can't see it now. Office restrictions). Any idea? -- Regards. David Harel, == Home office +972 77 7657645 Cellular: +972 54 4534502 Snail Mail: Amuka D.N Merom Hagalil 13802 Israel Email: harel...@ergolight-sw.com
Re: [SQL] User function that returns a set of rows.
On Mon, May 24, 2010 at 09:33:35PM +0300, David Harel wrote: >When I tried it from the shell I got a nasty error message about that I am >not in an environment to receive a set ??? (can't see it now. Office >restrictions). > >Any idea? Your query should say something like "SELECT * FROM select_business_types()". You'll get that error if you instead say "SELECT select_business_types()". -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [SQL] safely exchanging primary keys?
Louis-David Mitterrand wrote: >> > 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); >> Nice, thanks. > Ah, but this won't work as the UNIQUE PK constraint is in force. Oh, yes, you're right, I didn't have that premise in mind. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
