Re: [GENERAL] Auto Ordering

At 21:00 +0200 on 01/11/1999, amy cheng wrote:

> then, why use int, isn't char better? (almost no need for batch).

It all depends in the application. First, one must note that a
variable-length char takes more space (More than four bytes), and also,
variable-length fields have a slight performance penalty in retrieval.

Second, the question is what you want to do with it. I once had an
application in which the data items were lines in a piece of text. One
could add a line anywhere. So, if you wanted to insert a line between line
number 340 and line number 420, it was easy to find a number in between -
the average of the two numbers (420+340)/2 = 380. This would give you some
space for later addition between line 340 and 380 - using the same formula
- or between 380 and 420.

I think doing such a calculation in text is a bit more awkward. But it all
depends on whether you need to allow inserts on a regular basis, and assign
the orders manually. It's a question of design.

BTW, I think my suggestion for batch renumbering won't work in all cases. I
think a better alternative would be:

SELECT the_order as old_order, 0 as new_order
INTO TABLE temp_numbers
FROM questions
ORDER BY old_order;

CREATE SEQUENCE new_seq INCREMENT 100 START 100;

UPDATE temp_numbers
SET new_order = nextval( 'new_seq' );

UPDATE questions
SET the_order = temp_numbers.new_order
WHERE questions.the_order = temp_numbers.old_order;

DROP SEQUENCE new_seq;
DROP TABLE temp_numbers;

I also think this can be done more easily in a pl/pgsql function, but I am
not exactly an expert on them.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma