Re: cases in rules problem - Mailing list 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