2

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?

Kliver Max
  • 5,107
  • 22
  • 95
  • 148
  • 1
    http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html says trigger can be set on views. But AFAIK this is a new feature in PG 9.1. – LisMorski Jul 26 '12 at 08:14
  • The function seems to be cut off after the `WHERE`. Please update that part. – A.H. Jul 26 '12 at 08:23
  • 1
    Kliver, as @LisMorski said you must be using PostgreSQL 9.1 to use triggers on views. You are using an older version. Upgrade (preferably) or if you really can't upgrade, use a `RULE` instead; see the links in my answer. – Craig Ringer Jul 26 '12 at 08:52
  • User has posted a follow-up question here, in case anyone's reading this later: http://stackoverflow.com/questions/11681852/how-make-updatable-view-in-postgesql – Craig Ringer Jul 27 '12 at 06:03
  • For your "UPDATE" question: to change the column name from `st_transform` to `the_geom`, add `AS the_geom` to your select statement. – Mike T Jul 30 '12 at 02:24

1 Answers1

7

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Can you give example how to make trigger with RULE? – Kliver Max Jul 26 '12 at 09:06
  • @KliverMax Triggers and rules are different things. You don't make a trigger with a rule, you use a `DO INSTEAD` rule to make an updatable view directly via rewriting. If you can't do that by reading the documentation I linked to above then I *very* strongly recommend that you upgrade to PostgreSQL 9.1 and use a view trigger, because rules are *not* suitable for beginners. There are examples in the documentation. – Craig Ringer Jul 26 '12 at 09:19
  • It too dificult. I create trigger to table instead of trigger to view. – Kliver Max Jul 26 '12 at 10:10