class Janko::Upsert
dba.stackexchange.com/questions/13468/most-idiomatic-way-to-implement-upsert-in-postgresql-nowadays stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/8702291#8702291 stackoverflow.com/questions/17575489/postgresql-cte-upsert-returning-modified-rows
Public Instance Methods
cleanup()
click to toggle source
# File lib/janko/upsert.rb, line 43 def cleanup return unless prepared? connection.exec("DEALLOCATE \"#{@prepared}\"") @prepared = nil self end
default_state()
click to toggle source
# File lib/janko/upsert.rb, line 13 def default_state { collector: MergeResult.new } end
prepare()
click to toggle source
# File lib/janko/upsert.rb, line 21 def prepare return(self) if prepared? @prepared = "upsert_#{SecureRandom.hex(8)}" connection.prepare(@prepared, query) self end
process()
click to toggle source
# File lib/janko/upsert.rb, line 35 def process raise(RuntimeError, "Can't #process without from_table") \ unless read_from_table? result.clear collect_result(exec_query) self end
push(*values)
click to toggle source
# File lib/janko/upsert.rb, line 28 def push(*values) raise(RuntimeError, "Can't #push when reading from a table.") \ if read_from_table? collect_result(exec_query(columns.pack(*values))) self end
result()
click to toggle source
# File lib/janko/upsert.rb, line 17 def result collector end
Private Instance Methods
collect_result(tuples)
click to toggle source
# File lib/janko/upsert.rb, line 56 def collect_result(tuples) return(self) if (tuples.count == 0) tuples.each { |t| result.push(t.delete(result_type_column), t) } end
columns_to_binds_with_types()
click to toggle source
# File lib/janko/upsert.rb, line 94 def columns_to_binds_with_types columns.to_typecast_binds end
exec_query(*binds)
click to toggle source
# File lib/janko/upsert.rb, line 69 def exec_query(*binds) return(connection.exec(query, *binds)) unless prepared? connection.exec_prepared(@prepared, *binds) end
insert_columns()
click to toggle source
# File lib/janko/upsert.rb, line 82 def insert_columns @insert_columns ||= columns.tagged(:insert) end
insert_columns_with_defaults()
click to toggle source
# File lib/janko/upsert.rb, line 90 def insert_columns_with_defaults insert_columns.to_list_with_defaults end
insert_fragment()
click to toggle source
# File lib/janko/upsert.rb, line 139 def insert_fragment return(<<-END) INSERT INTO #{table} (#{insert_columns.to_list}) SELECT #{insert_columns_with_defaults} FROM #{source_table} WHERE NOT EXISTS (SELECT 1 FROM upsert WHERE #{insert_key_clause}) END end
insert_key_clause()
click to toggle source
# File lib/janko/upsert.rb, line 106 def insert_key_clause key_columns.to_conditions("upsert", source_table) end
key_columns()
click to toggle source
# File lib/janko/upsert.rb, line 74 def key_columns @key_columns ||= columns.tagged(:key) end
prepared?()
click to toggle source
# File lib/janko/upsert.rb, line 65 def prepared? not @prepared.nil? end
query()
click to toggle source
# File lib/janko/upsert.rb, line 110 def query return(query_returning_nothing) if select_columns.empty? return(query_returning_all) if (returning == "all") return(query_returning_inserts) if (returning == "inserted") return(query_returning_updates) if (returning == "updated") query_returning_nothing end
query_returning_all()
click to toggle source
# File lib/janko/upsert.rb, line 180 def query_returning_all return(<<-END) #{query_with_returnable_tuples} SELECT 'inserted' AS #{result_type_column}, #{select_columns.to_list} FROM inserted UNION ALL SELECT 'updated' AS #{result_type_column}, #{select_columns.to_list} FROM upsert END end
query_returning_inserts()
click to toggle source
# File lib/janko/upsert.rb, line 162 def query_returning_inserts return(<<-END) #{query_with_returnable_tuples} SELECT 'inserted' AS #{result_type_column}, #{select_columns.to_list} FROM inserted END end
query_returning_nothing()
click to toggle source
# File lib/janko/upsert.rb, line 148 def query_returning_nothing return(<<-END) WITH #{source_fragment} upsert AS (#{upsert_fragment}) #{insert_fragment} END end
query_returning_updates()
click to toggle source
# File lib/janko/upsert.rb, line 171 def query_returning_updates return(<<-END) #{query_with_returnable_tuples} SELECT 'updated' AS #{result_type_column}, #{select_columns.to_list} FROM upsert END end
query_with_returnable_tuples()
click to toggle source
# File lib/janko/upsert.rb, line 155 def query_with_returnable_tuples return(<<-END) WITH #{source_fragment} upsert AS (#{upsert_fragment}), inserted AS (#{insert_fragment} RETURNING #{table}.*) END end
read_from_table?()
click to toggle source
# File lib/janko/upsert.rb, line 61 def read_from_table? not @from_table.nil? end
result_type_column()
click to toggle source
# File lib/janko/upsert.rb, line 52 def result_type_column "__type" end
select_columns()
click to toggle source
# File lib/janko/upsert.rb, line 86 def select_columns @select_columns ||= columns.tagged(:select) end
source_fragment()
click to toggle source
# File lib/janko/upsert.rb, line 122 def source_fragment return if read_from_table? return(<<-END) #{source_table} (#{columns.to_list}) AS (VALUES (#{columns_to_binds_with_types})), END end
source_table()
click to toggle source
# File lib/janko/upsert.rb, line 118 def source_table @from_table || "upsert_data" end
update_clause()
click to toggle source
# File lib/janko/upsert.rb, line 98 def update_clause update_columns.to_setters("upsert_updates", source_table) end
update_columns()
click to toggle source
# File lib/janko/upsert.rb, line 78 def update_columns @update_columns ||= columns.tagged(:update).not_tagged(:key) end
update_key_clause()
click to toggle source
# File lib/janko/upsert.rb, line 102 def update_key_clause key_columns.to_conditions("upsert_updates", source_table) end
upsert_fragment()
click to toggle source
# File lib/janko/upsert.rb, line 130 def upsert_fragment return(<<-END) UPDATE #{table} upsert_updates SET #{update_clause} FROM #{source_table} WHERE (#{update_key_clause}) RETURNING upsert_updates.* END end