| Subject: | PATCH: PostgreSQL: query all non-system db_schemas for tables (key: search_path) |
| Date: | Wed, 29 Sep 2010 16:19:00 +0200 |
| To: | bug-SQL-Translator [...] rt.cpan.org |
| From: | Tim Esselens <tim.esselens [...] gmail.com> |
disambiguation: It's possible to have different db_schemas in
PostgreSQL which can be set with search_path.
It has nothing to do with the schema definition of a table. It's just
another level of separation inside the db.
This patch will query all non system schemas for tables.
WARNING: since a tablename might not be unique unless fully qualified
(ie: with the schema prepended), this patch prepends the schema_name.
Therefore it might mess up conversions to other SQL dialects. YMMV.
The purpose of this patch is to be able to draw diagrams with tables
from different database schemas
Patch is only tested using sqlt -f DBI-PostgreSQL -t {HTML, Diagram}
Index: lib/SQL/Translator/Parser/DBI/PostgreSQL.pm
===================================================================
--- lib/SQL/Translator/Parser/DBI/PostgreSQL.pm (revision 1703)
+++ lib/SQL/Translator/Parser/DBI/PostgreSQL.pm (working copy)
@@ -73,9 +73,9 @@
);
my $table_select = $dbh->prepare(
- "SELECT oid,relname FROM pg_class WHERE relnamespace IN
- (SELECT oid FROM pg_namespace WHERE nspname='public')
- AND relkind='r';"
+ "SELECT c.oid,(select nspname from pg_namespace n where n.oid
= c.relnamespace) || '.' || relname as relname FROM pg_class c WHERE
c.relnamespace IN
+ (SELECT oid FROM pg_namespace WHERE nspname !~ '^pg_' and
nspname != 'information_schema')
+ AND relkind='r'"
);
my $fk_select = $dbh->prepare(
@@ -112,7 +112,6 @@
ON n.oid = c.relnamespace
WHERE pg_catalog.pg_table_is_visible(c.oid)
- AND n.nspname = ?
AND c.relname = ?
ORDER BY 1;
/) or die "Can't prepare: $@";
@@ -182,14 +181,15 @@
) || die $table->error;
}
- $fk_select->execute('public',$table_name) or die "Can't execute: $@";
+ my ($db_schema, $short_table_name) = split /\./o, $table_name;
+ $fk_select->execute($short_table_name) or die "Can't execute: $@";
my $fkeys = $fk_select->fetchall_arrayref({});
$DEBUG and print Dumper $fkeys;
for my $con (@$fkeys){
my $con_name = $con->{conname};
my $fields = $con->{fields};
my $reference_fields = $con->{reference_fields};
- my $reference_table = $con->{frelname};
+ my $reference_table = $db_schema . '.' . $con->{frelname};
my $on_upd = $con->{confupdtype};
my $on_del = $con->{confdeltype};
$table->add_constraint(
Message body is not shown because sender requested not to inline it.