| Subject: | RFE: Support Postgres schemas |
in set_up_table, the queries do not support namespaces/schemas. A
couple of queries will break if tables of the same name exist in
different namespaces.
I hacked up my personal copy, but it doesn't support older versions of
postgres. I'll provide the relevant bits here anyway...
[ .. no changes until after $catalog is initialized ]
my $schema = 'public';
if ($table =~ m|[.]|) {
($schema,$table) = split(/[.]/,$table,2);
}
# find primary key
my $sth = $dbh->prepare(<<"SQL");
SELECT indkey
FROM
${catalog}pg_index as I
join ${catalog}pg_class as C on (I.indrelid = C.oid)
join ${catalog}pg_namespace as N on (C.relowner = N.nspowner)
WHERE
I.indisprimary = true
and C.relname = ?
and N.nspname = ?
SQL
$sth->execute($table,$schema);
my %prinum = map { $_ => 1 } split ' ', $sth->fetchrow_array;
$sth->finish;
# find all columns
$sth = $dbh->prepare(<<"SQL");
SELECT a.attname, a.attnum
FROM pg_class c, pg_attribute a, pg_namespace n
WHERE c.relname = ?
AND n.nspname = ?
AND c.relowner = n.nspowner
AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
SQL
$sth->execute($table,$schema);
my $columns = $sth->fetchall_arrayref;
$sth->finish;
# find SERIAL type.
# nextval('"table_id_seq"'::text)
$sth = $dbh->prepare(<<"SQL");
SELECT adsrc
FROM
${catalog}pg_attrdef as A
JOIN ${catalog}pg_class as C on (A.adrelid = C.oid)
JOIN ${catalog}pg_namespace as N on (C.relowner = N.nspowner)
WHERE c.relname = ?
AND n.nspname = ?
SQL
$sth->execute($table,$schema);
my ($nextval_str) = $sth->fetchrow_array;
$sth->finish;
[... no changes for the rest of the method]