From: Steve Midgley <public@misuse.org>
To: A. R. Van Hook <hook@lota.us>
To: Jamie Tufnell <diesql@googlemail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: works but ...
Date: Mon, 10 Mar 2008 15:03:57 -0700
Message-ID: <20080310220416.74B892E028E@postgresql.org> (raw)
In-Reply-To: <47D5ACD0.9020603@lota.us>
References: <47D5ACD0.9020603@lota.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: public@misuse.org, hook@lota.us, diesql@googlemail.com
Subject: Re: works but ...
In-Reply-To: <20080310220416.74B892E028E@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox