CREATE OR REPLACE FUNCTION update_urlmapping() RETURNS TRIGGER AS $$
 DECLARE
  newurl TEXT;
  lng_list TEXT;
 BEGIN
  --SELECT id FROM script WHERE id IS NOT NULL;
  SELECT object.name || ',' || city.name_en || ',' || country.name_en INTO newurl FROM object 
    LEFT JOIN object_region AS city_conn ON (city_conn.objectid = object.objectid)
    LEFT JOIN region AS city ON (city.id = city_conn.regionid AND city.regiontypeid = 7)
    LEFT JOIN object_region AS country_conn ON (country_conn.objectid = object.objectid)
    LEFT JOIN region AS country ON (country.id = country_conn.regionid AND city.regiontypeid = 3)
   WHERE object.objectid = NEW.id;
  IF (newurl = '') IS NOT FALSE THEN
    RETURN NULL;
  END IF;
  UPDATE urlmapping SET forwardtourl = newurl WHERE objectid = NEW.id AND url != newurl;
  INSERT INTO urlmapping (url, objectid) VALUES (newurl, NEW.id);
  RETURN NULL;
 END
$$ LANGUAGE plpgsql;


CREATE TRIGGER update_urlmapping_trigger AFTER INSERT OR UPDATE ON structure FOR EACH ROW EXECUTE PROCEDURE update_urlmapping();


