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@xxxxxxxxxxxxxx | www.2ndQuadrant.it
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
