Module: UpdateValuesAll::Adapters::Postgres

Defined in:
lib/update_values_all/adapters/postgres.rb

Instance Method Summary collapse

Instance Method Details

#pg_update_values_all(data, key_to_match:, touch: false, sql_update_expression: 'updated_at = CURRENT_TIMESTAMP') ⇒ Object 



7
8
9
10
11
12
13
14
15
16
17
18
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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
 
# File 'lib/update_values_all/adapters/postgres.rb', line 7

def pg_update_values_all(data, key_to_match:, touch: false, sql_update_expression: 'updated_at = CURRENT_TIMESTAMP')
  keys = data.first.keys

  sql_values = +''
  data.each do |hash_row|
    hash_keys = []
    serialize_row =
      hash_row.map do |column_name, value|
        hash_keys << column_name
        column_type = type_for_attribute(column_name)
        connection.quote(column_type.serialize(value))
      end

    raise KeyError, "Wrong keys: #{(hash_keys - keys) | (keys - hash_keys)}" if hash_keys != keys

    sql_values << "(#{serialize_row.join(', ')}),"
  end
  sql_values.chop!

  updated_keys = keys.join(', ')

  sql_types = data[0].keys.index_with { |column_name| column_for_attribute(column_name).sql_type_metadata.sql_type }
  set_expr  =
    if block_given?
      +yield
    else
      keys
        .reject { |key| key == key_to_match }
        .map { |key| "#{key} = updated_data.#{key}::#{sql_types[key]}" }
        .join(', ')
    end
  only_changed_expr =
    if touch
      'TRUE'
    else
      keys
        .reject { |key| key == key_to_match }
        .map { |key| "#{table_name}.#{key} IS DISTINCT FROM updated_data.#{key}::#{sql_types[key]}" }
        .join(' OR ')
    end

  if sql_update_expression.present?
    set_expr << ", #{sql_update_expression}"
  end

  existing_data_sql =
    select("#{table_name}.#{key_to_match}") # rubocop:disable Gp/PotentialSqlInjection
      .where("#{key_to_match} IN (SELECT #{key_to_match} FROM updated_data)")
      .to_sql

  changed_ids =
    connection.query(<<~SQL).flatten # rubocop:disable Gp/PotentialSqlInjection
      WITH
        updated_data(#{updated_keys}) AS (
          VALUES #{sql_values}
        ),
        existing_data AS (
          #{existing_data_sql}
        )
      UPDATE #{table_name}
      SET #{set_expr}
      FROM updated_data JOIN existing_data ON existing_data.#{key_to_match} = updated_data.#{key_to_match}
      WHERE
            updated_data.#{key_to_match} = #{table_name}.#{key_to_match}
        AND #{table_name}.#{key_to_match} = existing_data.#{key_to_match}
        AND (#{only_changed_expr})
      RETURNING #{table_name}.#{key_to_match}
    SQL

  connection.query_cache.clear if connection.query_cache_enabled

  changed_ids
end