Committed by pilcrow <[email protected]>
Subject: [DBD::Pg 11/14] Refactor table_info type lookup
---
Pg.pm | 69 +++++++++++++++++++++++++++++--------------------------------------
1 file changed, 30 insertions(+), 39 deletions(-)
diff --git a/Pg.pm b/Pg.pm
index f2ca2bd..fc414e3 100644
--- a/Pg.pm
+++ b/Pg.pm
@@ -1201,7 +1201,8 @@ use 5.008001;
else {
# Default SQL
$extracols = q{,n.nspname AS pg_schema, c.relname AS
pg_table};
- my @search;
+ my @search = (q|c.relkind IN ('r', 'v')|, # No
sequences, etc. for now
+ q|NOT (quote_ident(n.nspname) ~
'^pg_(toast_)?temp_' AND NOT has_schema_privilege(n.nspname, 'USAGE'))|); #
No others' temp objects
my $showtablespace = ', quote_ident(t.spcname) AS
"pg_tablespace_name", quote_ident(t.spclocation) AS "pg_tablespace_location"';
if ($dbh->{private_dbdpg}{version} >= 90200) {
$showtablespace = ', quote_ident(t.spcname) AS
"pg_tablespace_name", quote_ident(pg_tablespace_location(t.oid)) AS
"pg_tablespace_location"';
@@ -1215,22 +1216,6 @@ use 5.008001;
push @search, 'c.relname ' . ($table =~
/[_%]/ ? 'LIKE ' : '= ') . $dbh->quote($table);
}
- my %typesearch = (
- TABLE => q{c.relkind = 'r' AND
n.nspname !~ '^pg_'},
- VIEW => q{c.relkind = 'v' AND
n.nspname !~ '^pg_'},
- 'SYSTEM TABLE' => q{c.relkind = 'r' AND
n.nspname IN ('pg_catalog', 'pg_toast')},
- 'SYSTEM VIEW' => q{c.relkind = 'v' AND
n.nspname IN ('pg_catalog', 'pg_toast')},
- 'LOCAL TEMPORARY' => q{c.relkind = 'r'
AND quote_ident(n.nspname) ~ '^pg_(toast_)?temp_'},
- );
- my $typespec = join ' OR ' =>
- grep {defined}
- map { s/^'//g; s/'$//g;
$typesearch{uc($_)} }
- split(',', $type || '');
- $typespec = $typespec
- ? "($typespec)"
- : "c.relkind IN ('r', 'v')"; # default any
table/view
- push @search, $typespec;
-
my $TSJOIN = 'pg_catalog.pg_tablespace t ON (t.oid =
c.reltablespace)';
if ($dbh->{private_dbdpg}{version} < 80000) {
$TSJOIN = '(SELECT 0 AS oid, 0 AS spcname, 0 AS
spclocation LIMIT 0) AS t ON (t.oid=1)';
@@ -1240,30 +1225,36 @@ use 5.008001;
SELECT NULL::text AS "TABLE_CAT"
, quote_ident(n.nspname) AS "TABLE_SCHEM"
, quote_ident(c.relname) AS "TABLE_NAME"
- , CASE WHEN c.relkind = 'v' THEN
- CASE WHEN quote_ident(n.nspname) in
('pg_catalog', 'pg_toast')
- THEN 'SYSTEM VIEW'
- ELSE 'VIEW'
- END
- ELSE
- CASE WHEN quote_ident(n.nspname) in
('pg_catalog', 'pg_toast')
- THEN 'SYSTEM TABLE'
- WHEN quote_ident(n.nspname) ~
'^pg_(toast_)?temp_'
- THEN 'LOCAL TEMPORARY'
- ELSE 'TABLE'
- END
- END AS "TABLE_TYPE"
+ -- any temp table or temp view is LOCAL TEMPORARY for us
+ , CASE WHEN quote_ident(n.nspname) ~ '^pg_(toast_)?temp_'
THEN
+ 'LOCAL TEMPORARY'
+ WHEN c.relkind = 'r' THEN
+ CASE WHEN quote_ident(n.nspname) ~ '^pg_' THEN
+ 'SYSTEM TABLE'
+ ELSE 'TABLE'
+ END
+ ELSE CASE WHEN quote_ident(n.nspname) ~ '^pg_' THEN
+ 'SYSTEM VIEW'
+ ELSE 'VIEW'
+ END
+ END AS "TABLE_TYPE"
, d.description AS "REMARKS" $showtablespace $extracols
- FROM pg_catalog.pg_class AS c
- LEFT JOIN pg_catalog.pg_description AS d
- ON (c.oid = d.objoid AND c.tableoid = d.classoid AND
d.objsubid = 0)
- LEFT JOIN pg_catalog.pg_namespace n ON (n.oid =
c.relnamespace)
- LEFT JOIN $TSJOIN
- WHERE $whereclause
- --- exclude others' TEMPORARY tables (and views, for
that matter)
- AND NOT (quote_ident(n.nspname) ~ '^pg_(toast_)?temp_'
AND NOT has_schema_privilege(n.nspname, 'USAGE'))
- ORDER BY "TABLE_TYPE", "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME"
+ FROM pg_catalog.pg_class AS c
+ LEFT JOIN pg_catalog.pg_description AS d
+ ON (c.oid = d.objoid AND c.tableoid = d.classoid AND
d.objsubid = 0)
+ LEFT JOIN pg_catalog.pg_namespace n ON (n.oid =
c.relnamespace)
+ LEFT JOIN $TSJOIN
+ WHERE $whereclause
+ ORDER BY "TABLE_TYPE", "TABLE_CAT", "TABLE_SCHEM",
"TABLE_NAME"
};
+
+ if (defined($type) and length($type)) {
+ my $type_restrict = join ', ' =>
+ map { $dbh->quote($_)
unless /^'/ }
+ grep {length}
+ split(',', $type);
+ $tbl_sql = qq{SELECT * FROM ($tbl_sql) ti WHERE
"TABLE_TYPE" IN ($type_restrict)};
+ }
}
my $sth = $dbh->prepare( $tbl_sql ) or return undef;
$sth->execute();
--
1.8.4
