Re: PG Admin - Mailing list pgsql-general

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@comcast.net>
To: <pgsql-general@postgresql.org>
Cc: "Bob Pawley" <rjpawley@shaw.ca>; "Berend Tober"
<btober@seaworthysys.com>
Sent: Monday, December 04, 2006 7:58 PM
Subject: Re: [GENERAL] PG Admin


> >
>> ----- Original Message -----
>> From: "Berend Tober" <btober@seaworthysys.com>
>> To: "Bob Pawley" <rjpawley@shaw.ca>
>> Cc: "pgsql general" <pgsql-general@postgresql.org>
>> 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@comcast.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>