Sha256: b8e0cf3b08523c7212217f1f0595f84c660ed51fb06623bda52921162379f6dd
Contents?: true
Size: 1.98 KB
Versions: 12
Compression:
Stored size: 1.98 KB
Contents
# frozen_string_literal: true
class <%= @migration_class_name %> < ActiveRecord::Migration<%= ActiveRecord::VERSION::MAJOR < 5 ? '' : '[5.0]' %>
def up
execute <<-SQL
CREATE OR REPLACE FUNCTION metka_refresh_<%= view_name %>_materialized_view() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF TG_OP = 'INSERT' AND
(<%= source_columns.map { |column| "NEW.#{column} IS NOT NULL" }.join(' OR ') %>) THEN
REFRESH MATERIALIZED VIEW CONCURRENTLY <%= view_name %>;
END IF;
IF TG_OP = 'UPDATE' AND
(<%= source_columns.map { |column| "OLD.#{column} IS DISTINCT FROM NEW.#{column}" }.join(' OR ') %>) THEN
REFRESH MATERIALIZED VIEW CONCURRENTLY <%= view_name %>;
END IF;
IF TG_OP = 'DELETE' AND
(<%= source_columns.map { |column| "OLD.#{column} IS NOT NULL" }.join(' OR ') %>) THEN
REFRESH MATERIALIZED VIEW CONCURRENTLY <%= view_name %>;
END IF;
RETURN NEW;
END $$;
DROP MATERIALIZED VIEW IF EXISTS <%= view_name %>;
CREATE MATERIALIZED VIEW <%= view_name %> AS
SELECT
tag_name,
COUNT(*) AS taggings_count
FROM (
SELECT UNNEST
(<%= source_columns.join(' || ') %>) AS tag_name
FROM
<%= source_table_name %>
) subquery
GROUP BY
tag_name;
CREATE UNIQUE INDEX idx_<%= source_table_name %>_<%= source_columns_names %> ON <%= view_name %>(tag_name);
CREATE TRIGGER metka_on_<%= source_table_name %>_<%= source_columns_names %>
AFTER UPDATE OR INSERT OR DELETE ON <%= source_table_name %> FOR EACH ROW
EXECUTE PROCEDURE metka_refresh_<%= view_name %>_materialized_view();
SQL
end
def down
execute <<-SQL
DROP TRIGGER IF EXISTS metka_on_<%= source_table_name %>_<%= source_columns_names %> ON <%= source_table_name %>;
DROP FUNCTION IF EXISTS metka_refresh_<%= view_name %>_materialized_view;
DROP MATERIALIZED VIEW IF EXISTS <%= view_name %>;
SQL
end
end
Version data entries
12 entries across 12 versions & 1 rubygems