Re: cases in rules problem
От | Darnell Brawner |
---|---|
Тема | Re: cases in rules problem |
Дата | |
Msg-id | 0C920FDF-5859-41DF-B6EC-2C85BCE76DE0@smackdabstudios.com обсуждение исходный текст |
Ответ на | Re: cases in rules problem (Erik Jones <erik@myemma.com>) |
Список | pgsql-general |
Ok i gave up just keeping this in rules so i did this CREATE OR REPLACE RULE version AS ON UPDATE TO vmain DO INSTEAD select version2 (OLD.id,NEW.title,OLD.parent_id,NEW.public,OLD.public); CREATE OR REPLACE FUNCTION version2(int,varchar,int,bool,bool) RETURNS VOID LANGUAGE 'plpgsql' AS ' DECLARE _id ALIAS FOR $1; _title ALIAS FOR $2; _parent_id ALIAS FOR $3; _n_public ALIAS FOR $4; _o_public ALIAS FOR $5; BEGIN IF _n_public <> true or _o_public <> false THEN update main set public=true where id=_id; ELSE INSERT INTO main(parent_id,title,public) VALUES (_parent_id,_title,false); END IF; RETURN; END '; But when i run update vmain set title='tah4' where id=6 which in theory should insert a new row with parent_id=2, title='tah4' and public = false instead i get this error: [UPDATE - 0 row(s), 0.003 secs] [Error Code: 0, SQL State: 23505] ERROR: duplicate key violates unique constraint "firstkey" On Oct 26, 2007, at 12:04 PM, Erik Jones wrote: > On Oct 26, 2007, at 10:28 AM, Darnell Brawner wrote: > >> 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. > > The problem here is that CASE statements go in queries, not around > them. That leave two options: either create two rules, one for > each case, or go ahead and create a function that gets fired by > either a rule or a trigger. As far as managing the trigger on a > lot of tables, you can script that and I think you'll find that > easier to manage than multiple rules on each table. > > Erik Jones > > Software Developer | Emma® > erik@myemma.com > 800.595.4401 or 615.292.5888 > 615.292.0777 (fax) > > Emma helps organizations everywhere communicate & market in style. > Visit us online at http://www.myemma.com > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
В списке pgsql-general по дате отправления: