[SQL] safely exchanging primary keys?

2010-05-24 Thread 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;

Re: [SQL] safely exchanging primary keys?

2010-05-24 Thread Louis-David Mitterrand
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

Re: [SQL] safely exchanging primary keys?

2010-05-24 Thread Rob Sargent
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

Re: [SQL] safely exchanging primary keys?

2010-05-24 Thread Tom Lane
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

Re: [SQL] safely exchanging primary keys?

2010-05-24 Thread Tim Landscheidt
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

Re: [SQL] safely exchanging primary keys?

2010-05-24 Thread Louis-David Mitterrand
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

Re: [SQL] safely exchanging primary keys?

2010-05-24 Thread Louis-David Mitterrand
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

[SQL] User function that returns a set of rows.

2010-05-24 Thread David Harel
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

Re: [SQL] User function that returns a set of rows.

2010-05-24 Thread Joshua Tolley
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

Re: [SQL] safely exchanging primary keys?

2010-05-24 Thread Tim Landscheidt
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