Return full foreign key information using the pg system tables, including
:name, :on_delete, :on_update, and :deferrable entries in the hashes.
def foreign_key_list(table, opts={})
m = output_identifier_meth
im = input_identifier_meth
schema, table = schema_and_table(table)
range = 0...32
base_ds = metadata_dataset.
where(:cl__relkind=>'r', :co__contype=>'f', :cl__relname=>im.call(table)).
from(:pg_constraint___co).
join(:pg_class___cl, :oid=>:conrelid)
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 schema
ds = ds.join(:pg_namespace___nsp, :oid=>:cl__relnamespace).
where(:nsp__nspname=>im.call(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