Module: PGSpecHelper::Triggers

Included in:
PGSpecHelper
Defined in:
lib/pg_spec_helper/triggers.rb

Defined Under Namespace

Classes: UnexpectedActionOrientationError, UnexpectedActionTimingError, UnexpectedConditionsError, UnexpectedEventManipulationError

Instance Method Summary collapse

Instance Method Details

#create_trigger(schema_name, table_name, name, action_timing:, event_manipulation:, action_orientation:, function_schema:, function_name:, action_condition: nil, action_reference_old_table: nil, action_reference_new_table: nil) ⇒ Object

create a postgres trigger



19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
# File 'lib/pg_spec_helper/triggers.rb', line 19

def create_trigger schema_name, table_name, name, action_timing:, event_manipulation:, action_orientation:, function_schema:, function_name:, action_condition: nil, action_reference_old_table: nil, action_reference_new_table: nil
  unless [:insert, :delete, :update].include? event_manipulation
    raise UnexpectedEventManipulationError, event_manipulation
  end

  unless action_condition.nil? || action_condition.is_a?(String)
    raise UnexpectedConditionsError, "expected String but got `#{action_condition}`"
  end

  unless [:row, :statement].include? action_orientation
    raise UnexpectedActionOrientationError, action_orientation
  end

  unless [:before, :after, :instead_of].include? action_timing
    raise UnexpectedActionTimingError, action_timing
  end

  # "INSTEAD OF/BEFORE/AFTER" "INSERT/UPDATE/DELETE"
  timing_sql = "#{action_timing.to_s.sub("_", " ")} #{event_manipulation}".upcase

  condition_sql = action_condition.nil? ? "" : "WHEN (#{action_condition})"

  temp_tables = []
  unless action_reference_old_table.nil?
    temp_tables << "OLD TABLE AS #{action_reference_old_table}"
  end
  unless action_reference_new_table.nil?
    temp_tables << "NEW TABLE AS #{action_reference_new_table}"
  end
  temp_tables_sql = temp_tables.any? ? "REFERENCING #{temp_tables.join(" ")}" : ""

  connection.exec <<~SQL
    -- trigger names only need to be unique for this table
    CREATE TRIGGER #{name}
      #{timing_sql} ON #{schema_name}.#{table_name} #{temp_tables_sql}
        FOR EACH #{action_orientation}
          #{condition_sql}
          EXECUTE FUNCTION #{function_schema}.#{function_name}();
  SQL
end

#get_trigger_names(schema_name, table_name) ⇒ Object

return a list of trigger names for the provided table



61
62
63
64
65
66
67
68
69
70
71
72
73
 
# File 'lib/pg_spec_helper/triggers.rb', line 61

def get_trigger_names schema_name, table_name
  # get the trigger names
  rows = connection.exec(<<~SQL, [schema_name.to_s, table_name.to_s])
    SELECT
    trigger_name
    FROM
      information_schema.triggers
    WHERE
      event_object_schema = $1
      AND event_object_table = $2
  SQL
  rows.map { |r| r["trigger_name"].to_sym }
end