This is basically what I have done. However it is not particularly stable
and is inelegant.
The serial number is close to what I need except it becomes tied to the
information.
The row numbering on the PG Admin version 1.6.1 performs the same operation
that I am looking for. Is there some way of using that? For instance the
devices would be numbered coresponding to the row number that is already
being generated.
Bob
----- Original Message -----
From: "Adrian Klaver" <aklaver@xxxxxxxxxxx>
To: <pgsql-general@xxxxxxxxxxxxxx>
Cc: "Bob Pawley" <rjpawley@xxxxxxx>; "Berend Tober"
<btober@xxxxxxxxxxxxxxxx>
Sent: Monday, December 04, 2006 7:58 PM
Subject: Re: [GENERAL] PG Admin
>
----- Original Message -----
From: "Berend Tober" <btober@xxxxxxxxxxxxxxxx>
To: "Bob Pawley" <rjpawley@xxxxxxx>
Cc: "pgsql general" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, December 04, 2006 7:15 PM
Subject: Re: [GENERAL] PG Admin
> Bob Pawley wrote:
>> Your missing the point.
>>
>> I am creating a design system for industrial control.
>>
>> The control devices need to be numbered. The numbers need to be
>> sequential. If the user deletes a device the numbers need to
>> regenerate
>> to again become sequential and gapless.
>
> Could you explain what it is about industrial control that requires the
> reassignment of numbers? Seems to me to make for confusion because over
> time, you then have a particular instrument referred to by different
> identifiers. So if you had other data, such as written logs, shop floor
> design diagrams, or other data not included in the data base, for
> example, you'ld have the problem of keeping track of which instruments
> were really being talked about because the names (identifying number,
> that is) keep changing.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
On Monday 04 December 2006 07:39 pm, Bob Pawley wrote:
I am talking about designing the control system.
No one makes a perfect design at first go. Devices are deleted and others
added. Until the end of the design stage the numbers need to be
sequential
with no gaps. After the design the numbers of each device are static and
new devices are added to the sequence or fill in for abandoned devices -
but that is another, separate problem.
But that is beside the point. What I am looking for is a gapless sequence
generator which has the ability to justify for deletions as well as
additions.
What I am looking for is a very simple adaptation of the serial function.
All that I need it to do is to justify for design changes and not care
that
if it is reassinged to a different device. The fact that a particular
device may, by happenstance, change it's assigned number - once twice or
multiple times, during the design stage, is of no consequence - as long
as
the totallity of numbers assigned are sequential and gapless.
Bob
I see now. My thought would to hold the device numbers in a regular
integer
column. Have an AFTER INSERT/UPDATE/DELETE trigger that does a count on
the
table and renumbers all the rows in the id column. This is the brute
force
method. The alternative would be to search for the gaps and renumber from
the
first gap up.
--
Adrian Klaver
aklaver@xxxxxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
