postgresql rule to trigger

Question

I have two spatial tables and I am mirroring the data between the two table with a postgres rule:

 CREATE OR REPLACE RULE create_centroid AS ON INSERT TO csg.client_area_request DO INSERT INTO csg.stats (date_needed, date_requested, notice_period, project_name, project_reference, the_geom_webmercator) VALUES (new.date_needed, new.date_requested, new.notice_period, new.project_name, new.project_reference, st_centroid(new.the_geom_webmercator));

Is there any way to do the same process with trigger?

Answer 1

Create the procedural function as

CREATE OR REPLACE create_centroid()
RETURNS TRIGGER AS 
$body$
BEGIN
 INSERT INTO csg.stats (date_needed, date_requested, notice_period, project_name, project_reference, the_geom_webmercator) VALUES (NEW.date_needed, NEW.date_requested, NEW.notice_period, NEW.project_name, NEW.project_reference, st_centroid(NEW.the_geom_webmercator));
 RAISE NOTICE 'Inserted';
 RETURN null;
END
$body$ language plpgsql volatile;

and create trigger (as was explained by the @zimmi) as

CREATE TRIGGER create_centroid AFTER INSERT ON csg.client_area_request
 FOR EACH ROW EXECUTE PROCEDURE create_centroid();

I was using this trigger to update the stats table. Should work.

• Tamas Kosa: I got an error on this. I can save it if I change to "create or replace function..." But when I add a new line to my table the trigger generate an error: "infinite recursion detected in rules for relation"
• Tamas Kosa: I think both triggers are good. I tested my personal postgres db and there they just work fine (with some changes) but somehow they don't work in cartodb...
• Zia: @TamasKosa Sorry, but I have tested for postgres db only. You should instead change the question to mention cartodb.

Answer 2

CREATE OR REPLACE FUNCTION create_centroid() RETURNS TRIGGER
$$
 BEGIN
 INSERT INTO csg.stats (date_needed, date_requested, notice_period, project_name, project_reference, the_geom_webmercator) VALUES (NEW.date_needed, NEW.date_requested, NEW.notice_period, NEW.project_name, NEW.project_reference, st_centroid(NEW.the_geom_webmercator));
 END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER create_centroid AFTER INSERT ON csg.client_area_request
 FOR EACH ROW EXECUTE PROCEDURE create_centroid();

should work just fine (not tested though)

• Tamas Kosa: I got an error on this. I don't know where is the mistake
• Tamas Kosa: I think both triggers are good. I tested my personal postgres db and there they just work fine (with some changes) but somehow they don't work in cartodb...
• Michal Zimmermann: I thought you were asking for postgresql solution (according to tags of this question).