PostgreSQL Counter Cache

postgres_counter_cache.sql

-- Courtesy
-- http://shuber.io/porting-activerecord-counter-cache-behavior-to-postgres/

-- Counter-cache implementation for foreign-key of type UUID

CREATE FUNCTION increment_counter(table_name text, column_name text, id integer, step integer)  
  RETURNS VOID AS $$
    DECLARE
      table_name text := quote_ident(table_name);
      column_name text := quote_ident(column_name);
      conditions text := ' WHERE id = $1';
      updates text := column_name || '=' || column_name || '+' || step;
    BEGIN
      EXECUTE 'UPDATE ' || table_name || ' SET ' || updates || conditions
      USING id;
    END;
  $$ LANGUAGE plpgsql;

CREATE FUNCTION counter_cache()  
RETURNS trigger AS $$
  DECLARE
    table_name text := quote_ident(TG_ARGV[0]);
    counter_name text := quote_ident(TG_ARGV[1]);
    fk_name text := quote_ident(TG_ARGV[2]);
    fk_value_old uuid := to_jsonb(OLD) ->> fk_name;
    fk_value_new uuid := to_jsonb(NEW) ->> fk_name;
    -- Avoid comparsion with NULL, because conditions will not be met when value of FK will actually be changed
    -- Use blank value for uuid type to perform typesafe comparsion in case when "coalesce" will return second value as a fallback for NUL
    null_uuid_value uuid := '00000000-0000-0000-0000-000000000000';
    fk_changed boolean := coalesce(fk_value_old, null_uuid_value) != coalesce(fk_value_new, null_uuid_value);
    record record;
  BEGIN
    IF TG_OP = 'DELETE' OR fk_changed THEN
      record := OLD;
      PERFORM update_counter_cache_value(table_name, counter_name, fk_value_old, -1);
    END IF;

    IF TG_OP = 'INSERT' OR fk_changed THEN
      record = NEW;
      PERFORM update_counter_cache_value(table_name, counter_name, fk_value_new, 1);
    END IF;

    RETURN record;
  END;
$$ LANGUAGE plpgsql;


/*

Example use:

CREATE TABLE posts (  
  id              uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  title           text NOT NULL,
  body            text NOT NULL,
  comments_count  integer NOT NULL DEFAULT 0
);

CREATE TABLE comments (  
  id       uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  post_id  uuid NOT NULL REFERENCES posts(id),
  body     text NOT NULL
);

CREATE TRIGGER update_post_comments_count  
  AFTER INSERT OR UPDATE OR DELETE ON comments
  FOR EACH ROW EXECUTE PROCEDURE counter_cache('posts', 'comments_count', 'post_id');
  
*/