cases in rules problem
От | Darnell Brawner |
---|---|
Тема | cases in rules problem |
Дата | |
Msg-id | E007BFAA-6F16-4449-AFD6-596DE264BD04@smackdabstudios.com обсуждение исходный текст |
Ответы |
Re: cases in rules problem
|
Список | pgsql-general |
I am trying to make a sql based versioning system. I am working on a Ruby on Rails project and am using a plugin called hobo the plugin can do some nice things but over all its lame but thats what i got to work with. The problem is hobo does a lot of work for you but the database most be in a standard format to use it. so my idea for a sql versioning work around was this. CREATE TABLE main( id serial CONSTRAINT firstkey PRIMARY KEY, parent_id int, title varchar(30), public boolean default false ); INSERT INTO main(parent_id,title,public) VALUES (1,'blah',true), (1,'tah',false), (1,'blah2',false), (1,'blah3',false), (2,'tah2',false), (2,'tah3',true); CREATE VIEW vmain as (SELECT * FROM main WHERE public=true ORDER BY id DESC) UNION (SELECT * FROM main WHERE id IN (select max(id) from main group by parent_id) ORDER BY id DESC) CREATE OR REPLACE RULE main_up AS ON UPDATE TO vmain DO INSTEAD INSERT INTO main(parent_id,title,public) VALUES(NEW.parent_id,NEW.title,false); the result of the view should be all rows with public as true and one false for each new parent_id if any that must have a higher id than the true one. So on the web server, someone of level writer can edit something a superuser has created but what happens is it puts the update into the view hits the rule and makes a dup in the main table with public set to false so no one on the outside can see it. And basically the most rows that show up will be the public on and the highest id private one i don't really care about them rolling back versions. My problem is when the admin wants to approve the private row. I tryed CREATE OR REPLACE RULE main_up AS ON UPDATE TO vmain DO INSTEAD CASE NEW.public = true and OLD.public = false THEN UPDATE main set public=true where id=NEW.id ELSE INSERT INTO main(parent_id,title,public) VALUES(NEW.parent_id,NEW.title,false); But i can't seem to put CASE statements in a rule is there any why i can do then with out having to create a function and rule that fires it? This has to go on alot of table.
В списке pgsql-general по дате отправления: