CODEWITHSUNDEEP
postgresqlpostgis
Kliver Max
Kliver Max

Reputation: 5299

How to update table when view is updated?

I want to update table when her view is updated. I use postgresql/postgis.

I create view.

CREATE VIEW filedata_view
AS SELECT num, id, ST_TRANSFORM(the_geom,900913)
FROM filedata

And now when its updated i want to update TABLE with this data. But i heared that triggers cant be puted in VIEW. So how to do this?

Now i use this function

CREATE OR REPLACE FUNCTION update_table() RETURNS TRIGGER AS ' 
BEGIN 
UPDATE filedata SET id=NEW.id, the_geom=ST_TRANSFORM(NEW.st_transform,70066) where num=NEW.num ;
END;
' LANGUAGE plpgsql;

its fine. But another problem. How to add trigger to view i do this 

CREATE TRIGGER up_table AFTER UPDATE ON filedata_view
FOR EACH ROW EXECUTE PROCEDURE update_table ();

but get error 

ERROR: "filedata_view" is not a table.

UPDATE

How to set column name AS SELECT num, id, ST_TRANSFORM(the_geom,900913) if i use this i get columns : num,id and st_transform. How to set third column's name to the_geom?

Upvotes: 2

Views: 3256

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324465

For PostgreSQL 9.1 and above, use a DO INSTEAD trigger on the view. View triggers are much less difficult to get right and are less prone to weird problems with multiple evaluation, etc.

For PostgreSQL 9.0 and below, you should use the rule system - see CREATE RULE ... DO INSTEAD. It is generally better to update to 9.1 and use a view trigger if you possibly can, especially for new users. Rules are tricky things.

Upvotes: 7

Related Questions