From: | "Aaron J(dot) Seigo" <aaron(at)gtv(dot)ca> |
---|---|
To: | "Shawn T(dot) Walker" <shawn(at)netcrafters(dot)com>, pgsql-general(at)postgreSQL(dot)org |
Subject: | Re: [GENERAL] Auto Ordering |
Date: | 1999-10-27 18:44:04 |
Message-ID: | 99102713125801.04841@stilborne |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | soundar rajan | 1999-10-27 19:12:35 | foreign key |
Previous Message | Stuart Rison | 1999-10-27 18:41:42 | Re: [GENERAL] Auto Ordering |