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(dot)broccolo(at)2ndQuadrant(dot)it | www.2ndQuadrant.it