Re: [GENERAL] Auto Ordering

hi...

> Table: questions
> question_id SERIAL
> page int
> order int
> question varchar(100)
> answers text
>
> The field order is how the questions are sorted.  How can I change change
> question 5 to be number one and have all the other questions re-order
> themselves?
>


first, get rid of that SERIAL declaration, you'll have more trouble than
you need getting your shuffling done otherwise...second, rename field order to
something it won't balk at (eg order_of)... ok..

 you could create a trigger function that would check to see if there
are any records with the new question_id and if so, increment them by 1. this
would create the following cascade:

5 becomes 1. is there another record with 1? yes? then change it to 2.

1 becomes 2. is there another record with 2? yes? then change it to 3.

you get the picture.

or, perhaps simpler: write a pl/pgsql function that takes the oid of the record
(question_id = X) to be altered and what it will be altered to (call it
final_id) that increments records with question_id < X and > final_id
final_id then alters record with OID to final_id. 3 lines of code required (need
one to return a value);

drop function switcharoo(OID, int4,int4);
CREATE function switcharoo(OID, int4, int4) returns int2 as '
DECLARE
  target alias for $1;
  initial_id alias for $2;
  final_id alias for $3;
BEGIN
  update questions set question_id = question_id + 1 where
            question_id < initial_id and
             question_id + 1> final_id;
  update questions set question_id = final_id where oid = target;
 return 1;
END;' language 'plpgsql';


i'd do the latter. its simpler. but cascading triggers are so
gosh-darn neat. (btw, i tested this.. it works quite nice.. just remember to
always append "order by question_id" to your selects to get them in the
proper order)

--
Aaron J. Seigo
Sys Admin