def columns(table)
sql1 = %[
SELECT a.attname, i.indisprimary, i.indisunique
FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
WHERE bc.relkind = 'r' AND bc.relname = ? AND i.indrelid = bc.oid AND
i.indexrelid = ic.oid AND ic.oid = a.attrelid
]
sql2 = %[
SELECT a.attname, a.atttypid, a.attnotnull, a.attlen, format_type(a.atttypid, a.atttypmod)
FROM pg_class c, pg_attribute a, pg_type t
WHERE a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid AND c.relname = ?
]
sql3 = %[
SELECT pg_attrdef.adsrc, pg_attribute.attname
FROM pg_attribute, pg_attrdef, pg_class
WHERE pg_class.relname = ? AND
pg_attribute.attrelid = pg_class.oid AND
pg_attrdef.adrelid = pg_class.oid AND
pg_attrdef.adnum = pg_attribute.attnum
]
dbh = DBI::DatabaseHandle.new(self)
indices = {}
default_values = {}
dbh.select_all(sql3, table) do |default, name|
default_values[name] = default
end
dbh.select_all(sql1, table) do |name, primary, unique|
indices[name] = [primary, unique]
end
ret = []
dbh.execute(sql2, table) do |sth|
ret = sth.collect do |row|
name, pg_type, notnullable, len, ftype = row
indexed = false
primary = nil
unique = nil
if indices.has_key?(name)
indexed = true
primary, unique = indices[name]
end
type = ftype
pos = ftype.index('(')
decimal = nil
size = nil
if pos != nil
type = ftype[0..pos-1]
size = ftype[pos+1..-2]
pos = size.index(',')
if pos != nil
size, decimal = size.split(',', 2)
size = size.to_i
decimal = decimal.to_i
else
size = size.to_i
end
end
size = len if size.nil?
if POSTGRESQL_to_XOPEN.has_key?(type)
sql_type = POSTGRESQL_to_XOPEN[type][0]
else
sql_type = POSTGRESQL_to_XOPEN[nil][0]
end
row = {}
row['name'] = name
row['sql_type'] = sql_type
row['type_name'] = type
row['nullable'] = ! notnullable
row['indexed'] = indexed
row['primary'] = primary
row['unique'] = unique
row['precision'] = size
row['scale'] = decimal
row['default'] = default_values[name]
row
end
end
return ret
end