Method: Sequel::Postgres::DatabaseMethods#foreign_key_list

Defined in:
lib/sequel/adapters/shared/postgres.rb

#foreign_key_list(table, opts = OPTS) ⇒ Object

Return full foreign key information using the pg system tables, including :name, :on_delete, :on_update, and :deferrable entries in the hashes.



286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 286

def foreign_key_list(table, opts=OPTS)
  m = output_identifier_meth
  schema, _ = opts.fetch(:schema, schema_and_table(table))
  range = 0...32

  base_ds = metadata_dataset.
    from(:pg_constraint___co).
    join(:pg_class___cl, :oid=>:conrelid).
    where(:cl__relkind=>'r', :co__contype=>'f', :cl__oid=>regclass_oid(table))

  # We split the parsing into two separate queries, which are merged manually later.
  # This is because PostgreSQL stores both the referencing and referenced columns in
  # arrays, and I don't know a simple way to not create a cross product, as PostgreSQL
  # doesn't appear to have a function that takes an array and element and gives you
  # the index of that element in the array.

  ds = base_ds.
    join(:pg_attribute___att, :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, :co__conkey)).
    order(:co__conname, SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(:co__conkey, [x]), x]}, 32, :att__attnum)).
    select(:co__conname___name, :att__attname___column, :co__confupdtype___on_update, :co__confdeltype___on_delete,
           SQL::BooleanExpression.new(:AND, :co__condeferrable, :co__condeferred).as(:deferrable))

  ref_ds = base_ds.
    join(:pg_class___cl2, :oid=>:co__confrelid).
    join(:pg_attribute___att2, :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, :co__confkey)).
    order(:co__conname, SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(:co__conkey, [x]), x]}, 32, :att2__attnum)).
    select(:co__conname___name, :cl2__relname___table, :att2__attname___refcolumn)

  # If a schema is given, we only search in that schema, and the returned :table
  # entry is schema qualified as well.
  if schema
    ref_ds = ref_ds.join(:pg_namespace___nsp2, :oid=>:cl2__relnamespace).
      select_more(:nsp2__nspname___schema)
  end

  h = {}
  fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 
  ds.each do |row|
    if r = h[row[:name]]
      r[:columns] << m.call(row[:column])
    else
      h[row[:name]] = {:name=>m.call(row[:name]), :columns=>[m.call(row[:column])], :on_update=>fklod_map[row[:on_update]], :on_delete=>fklod_map[row[:on_delete]], :deferrable=>row[:deferrable]}
    end
  end
  ref_ds.each do |row|
    r = h[row[:name]]
    r[:table] ||= schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table])
    r[:key] ||= []
    r[:key] << m.call(row[:refcolumn])
  end
  h.values
end