Re: works but ...

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