Re: Rule Question

> I am trying to do something like this
>
> create table cats (a text,b text);
>
> create rule cats_test as on update to cats do set a = new.b;
>
> Can i manipulate column "a" sort of like this...  or is  there a
> better way.
I think the easiest way to do this is to use a trigger like this:

CREATE FUNCTION update_column() RETURNS TRIGGER AS $update_column$
     BEGIN
         IF TG_OP = 'INSERT' OR
             (TG_OP = 'UPDATE' AND
                 (NEW.b != OLD.b OR
                     (NEW.b IS NULL AND OLD.b IS NOT NULL) OR
                     (NEW.b IS NOT NULL AND OLD.b IS NULL)
                 )
         ) THEN
             NEW.a = NEW.b;
         END IF;
         RETURN NEW;
     END;
$update_column$ LANGUAGE plpgsql;

CREATE TRIGGER update_column BEFORE INSERT OR UPDATE ON cats
     FOR EACH ROW
     EXECUTE PROCEDURE update_column();

So for instance, if you insert a new "column b" value

INSERT INTO cats (b) VALUES ('byebye');

you'll get a='byebye' and b='byebye', and if you update this value

UPDATE cats SET b = 'goodbye' WHERE a = 'byebye';

you'll get a='goodbye' and b='goodbye'. Anyway, this is just an example.
I suggest that you look at the CREATE TRIGGER page in the documentation

http://www.postgresql.org/docs/9.2/static/sql-createtrigger.html

as you can also consider conditional triggers to be executed, for
example, only when the b column is updated.

Hope it can help.

Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it