functions.sql

CREATE OR REPLACE FUNCTION updated_timestamp()
RETURNS TRIGGER AS $$
BEGIN
   IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
      NEW.updated_at = now();
      RETURN NEW;
   ELSE
      RETURN OLD;
   END IF;
END;
$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION guard_created_timestamp()
RETURNS TRIGGER AS $$
BEGIN
   IF row(NEW.created_at) IS DISTINCT FROM row(OLD.created_at) THEN
      RAISE WARNING 'created_at can not be modified';
			NEW.created_at = OLD.created_at;
			return NEW;
   ELSE
      RETURN NEW;
   END IF;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_product_timestamp BEFORE UPDATE ON products FOR EACH ROW EXECUTE PROCEDURE updated_timestamp();
CREATE TRIGGER guard_created_at_on_products BEFORE UPDATE ON products FOR EACH ROW EXECUTE PROCEDURE guard_created_timestamp();

CREATE OR REPLACE FUNCTION touch_product_timestamp()
  RETURNS TRIGGER AS $$
BEGIN
  IF (TG_OP = 'DELETE') THEN
    UPDATE products SET updated_at = now() WHERE id = OLD.product_id;
  ELSE
    UPDATE products SET updated_at = now() WHERE id = NEW.product_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER touch_product_timestamp AFTER INSERT OR UPDATE OR DELETE ON product_aliases FOR EACH ROW EXECUTE PROCEDURE touch_product_timestamp();