1

A before insert/update trigger on one of my CartoDB tables is not being raised. Same trigger is working in a local installation of PostGIS.

This is my source table. And here, the trigger's code:

CREATE OR REPLACE FUNCTION update_geom_column()
RETURNS trigger AS
$BODY$
DECLARE
  coords_array varchar[] := ARRAY[2];
BEGIN
  if new.map_url is null then
    raise exception 'map_url cannot be null';
  end if;

  -- Get coords using regexp_matches
  select regexp_matches(NEW.map_url, 'center=(-?[\d]*\.[\d]*),(-?[\d]*\.[\d]*)&') into coords_array;
  NEW.the_geom = CDB_LatLng(coords_array[1]::numeric, coords_array[2]::numeric);

  return NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

drop trigger if exists update_geom_column_trigger on foursquare_checkins;
CREATE TRIGGER update_geom_column_trigger
BEFORE INSERT OR UPDATE
ON foursquare_checkins
FOR EACH ROW
EXECUTE PROCEDURE update_geom_column();

Every time there's a new insertion in my CartoDB table, all geometry fields of all rows are set to null. But the same trigger is working in my local PostGIS.

May it be something related with CartoDB itself?

2

It seems that your table is a Sync Table. Unfortunately, on each sync, the triggers of the sync tables are dropped.

Time ago we reported this into the CartoDB repo for keeping triggers through synchronizations, but it seems that the team hasn't worked on that yet: https://github.com/CartoDB/cartodb/issues/1941

  • Wow, and I was the one who opened the ticket... didn't remember that. Of course, that explains the problem. Silly me... Many thanks! – Jorge Arévalo Nov 23 '15 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.