Re: [GENERAL] Auto Ordering
От | Aaron J. Seigo |
---|---|
Тема | Re: [GENERAL] Auto Ordering |
Дата | |
Msg-id | 99102713125801.04841@stilborne обсуждение исходный текст |
Ответ на | Auto Ordering ("Shawn T. Walker" <shawn@netcrafters.com>) |
Список | 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
В списке pgsql-general по дате отправления: