Re: cases in rules problem - Mailing list pgsql-general

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