125: def columns(table)
126: sql1 = %[
127: select a.attname, i.indisprimary, i.indisunique
128: from pg_class bc inner join pg_index i
129: on bc.oid = i.indrelid
130: inner join pg_class c
131: on c.oid = i.indexrelid
132: inner join pg_attribute a
133: on c.oid = a.attrelid
134: where bc.relname = ?
135: and bc.relkind in ('r', 'v')
136: and pg_catalog.pg_table_is_visible(bc.oid);
137: ]
138:
139: sql2 = %[
140: SELECT a.attname, a.atttypid, a.attnotnull, a.attlen, format_type(a.atttypid, a.atttypmod)
141: FROM pg_catalog.pg_class c, pg_attribute a, pg_type t
142: WHERE a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid AND c.relname = ?
143: AND c.relkind IN ('r','v')
144: AND pg_catalog.pg_table_is_visible(c.oid)
145: ]
146:
147:
148:
149: sql3 = %[
150: SELECT pg_attrdef.adsrc, pg_attribute.attname
151: FROM pg_attribute, pg_attrdef, pg_catalog.pg_class
152: WHERE pg_catalog.pg_class.relname = ? AND
153: pg_attribute.attrelid = pg_catalog.pg_class.oid AND
154: pg_attrdef.adrelid = pg_catalog.pg_class.oid AND
155: pg_attrdef.adnum = pg_attribute.attnum
156: AND pg_catalog.pg_class.relkind IN ('r','v')
157: AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid)
158: ]
159:
160: dbh = DBI::DatabaseHandle.new(self)
161: dbh.driver_name = DBI::DBD::Pg.driver_name
162: indices = {}
163: default_values = {}
164:
165: dbh.select_all(sql3, table) do |default, name|
166: default_values[name] = default
167: end
168:
169: dbh.select_all(sql1, table) do |name, primary, unique|
170: indices[name] = [primary, unique]
171: end
172:
173:
174:
175: ret = []
176: dbh.execute(sql2, table) do |sth|
177: ret = sth.collect do |row|
178: name, pg_type, notnullable, len, ftype = row
179:
180: indexed = false
181: primary = nil
182: unique = nil
183: if indices.has_key?(name)
184: indexed = true
185: primary, unique = indices[name]
186: end
187:
188: typeinfo = DBI::DBD::Pg.parse_type(ftype)
189: typeinfo[:size] ||= len
190:
191: if POSTGRESQL_to_XOPEN.has_key?(typeinfo[:type])
192: sql_type = POSTGRESQL_to_XOPEN[typeinfo[:type]][0]
193: else
194: sql_type = POSTGRESQL_to_XOPEN[nil][0]
195: end
196:
197: row = {}
198: row['name'] = name
199: row['sql_type'] = sql_type
200: row['type_name'] = typeinfo[:type]
201: row['nullable'] = ! notnullable
202: row['indexed'] = indexed
203: row['primary'] = primary
204: row['unique'] = unique
205: row['precision'] = typeinfo[:size]
206: row['scale'] = typeinfo[:decimal]
207: row['default'] = default_values[name]
208: row['array_of_type'] = typeinfo[:array]
209:
210: if typeinfo[:array]
211: row['dbi_type'] =
212: DBI::DBD::Pg::Type::Array.new(
213: DBI::TypeUtil.type_name_to_module(typeinfo[:type])
214: )
215: end
216: row
217: end
218: end
219:
220: return ret
221: end