视图更新时如何更新表? [英] How to update table when view is updated?

问题描述

我想在更新她的视图时更新表格.我使用postgresql/postgis.

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

我创建视图.

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

现在，当它更新时，我想用此数据更新TABLE.但是我听说触发器不能放在VIEW中.那么该怎么做呢?

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?

现在我使用此功能

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 ();

但出现错误

ERROR: "filedata_view" is not a table.

更新

如果使用此列，如何设置列名AS SELECT num, id, ST_TRANSFORM(the_geom,900913)，我得到列:num，id和st_transform.如何将第三列的名称设置为the_geom?

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?

推荐答案

对于PostgreSQL 9.1及更高版本，请使用

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.

对于PostgreSQL 9.0及更低版本，您应该使用规则系统 -请参见创建规则...请勿执行.如果可能的话，通常最好更新到9.1并使用视图触发器，特别是对于新用户.规则是棘手的事情.

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.

这篇关于视图更新时如何更新表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！