Re: cases in rules problem

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(at)myemma(dot)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