counter_cache.sql

      CREATE FUNCTION maintain_realty_photos_count_trg() RETURNS TRIGGER AS
      $$
      BEGIN
        IF TG_OP IN ('UPDATE', 'DELETE') THEN
          UPDATE realties SET photos_count = photos_count - 1 WHERE id = old.realty_id;
          RETURN old;
        END IF;
        IF TG_OP IN ('INSERT', 'UPDATE') THEN
          UPDATE realties SET photos_count = photos_count + 1 WHERE id = new.realty_id;
          RETURN new;
        END IF;
      END
      $$
      LANGUAGE plpgsql;

      CREATE TRIGGER maintain_realty_photos_count_on_delete
        AFTER INSERT OR DELETE ON photos
        FOR EACH ROW
        EXECUTE PROCEDURE maintain_realty_photos_count_trg();

      CREATE TRIGGER maintain_realty_photos_count_on_update
        AFTER UPDATE ON photos
        FOR EACH ROW
        WHEN (old.realty_id IS DISTINCT FROM new.realty_id)
        EXECUTE PROCEDURE maintain_realty_photos_count_trg();