Date: Mon, 10 Mar 2008 15:03:57 -0700
To: "A. R. Van Hook" <hook@lota.us>,Jamie Tufnell <diesql@googlemail.com>
From: Steve Midgley <public@misuse.org>
Subject: Re: works but ...
Cc: pgsql-sql@postgresql.org
In-Reply-To: <47D5ACD0.9020603@lota.us>
References: <47D5ACD0.9020603@lota.us>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
Message-Id: <20080310220416.74B892E028E@postgresql.org>

At 02:49 PM 3/10/2008, A. R. Van Hook wrote:
>The following code seems to work but it leads to the following 
>question(s):
>Is there a sequence for each scid,item or is there one sequence that 
>must be reset
>when changing scid?
>
>    $cmd = "select setval('schItem_item_seq', (select max(item)+1 from 
> schItem where scid=$newScid))";
>    $result = $conn->exec($cmd);
>    $OK +=  cmp_eq($conn,PGRES_TUPLES_OK, $result->resultStatus);
>
>    $cmd = "insert into schItem (scid, value, iflag, outalts, sidate, 
> istid)
>            select $newScid,  i.value, i.iflag, i.outalts, i.sidate, 
> i.istid
>              from schItem i, schItem s
>            where (i.scid=$wrongScid and i.item >= $searchItem)
>              and (s.scid=$newScid)
>            group by i.value, i.iflag, i.outalts, i.sidate, i.istid, 
> i.scid";
>    $result = $conn->exec($cmd);
>    $OK +=  cmp_eq($conn,PGRES_COMMAND_OK, $result->resultStatus);
>
>    $cmd = "delete from schItem where scid = $wrongScid and item >= 
> $searchItem";
>
>ps
>    the full perl script is attached
>
>thanks again
>art
>
>
>--
>Arthur R. Van Hook

Hi Arthur,

I'm not totally clear on your question but generally speaking there is 
one sequence per primary key, by default on each table. So if you reset 
that key, then your table will start issuing keys at that new number. 
Another way to be more safe is to "+5" your sequence, so that even if a 
few inserts slip in, you're still ahead of the game..

Steve