[DBD::Pg 01/14] table_info() explicit SYSTEM TABLE|VIEW support

Committed by pilcrow <[email protected]>

Subject: [DBD::Pg 01/14] table_info() explicit SYSTEM TABLE|VIEW support

---
 Pg.pm | 60 ++++++++++++++++++++++++++++++++++++------------------------
 1 file changed, 36 insertions(+), 24 deletions(-)

diff --git a/Pg.pm b/Pg.pm
index f1e951c..c4a9fb7 100644
--- a/Pg.pm
+++ b/Pg.pm
@@ -1171,19 +1171,28 @@ use 5.008001;
 					 and (defined $type and $type eq '%')
 					) {
 			$tbl_sql = qq{
-                    SELECT
-                       NULL::text AS "TABLE_CAT"
-                     , NULL::text AS "TABLE_SCHEM"
-                     , NULL::text AS "TABLE_NAME"
-                     , 'TABLE'    AS "TABLE_TYPE"
-                     , 'relkind: r' AS "REMARKS" $extracols
-                    UNION
-                    SELECT
-                       NULL::text AS "TABLE_CAT"
-                     , NULL::text AS "TABLE_SCHEM"
-                     , NULL::text AS "TABLE_NAME"
-                     , 'VIEW'     AS "TABLE_TYPE"
-                     , 'relkind: v' AS "REMARKS" $extracols
+                    SELECT "TABLE_CAT"
+                         , "TABLE_SCHEM"
+                         , "TABLE_NAME"
+                         , "TABLE_TYPE"
+                         , "REMARKS"
+                    FROM
+                      (SELECT NULL::text AS "TABLE_CAT"
+                            , NULL::text AS "TABLE_SCHEM"
+                            , NULL::text AS "TABLE_NAME") dummy_cols
+                    CROSS JOIN
+                      (SELECT 'TABLE'        AS "TABLE_TYPE"
+                            , 'relkind: r'   AS "REMARKS"
+                       UNION
+                       SELECT 'SYSTEM TABLE'
+                            , 'relkind: r; nspname ~ ^pg_'
+                       UNION
+                       SELECT 'VIEW'
+                            , 'relkind: v'
+                       UNION
+                       SELECT 'SYSTEM VIEW'
+                            , 'relkind: v; nspname ~ ^pg_') type_info
+                     ORDER BY "TABLE_TYPE" ASC
                 };
 		}
 		else {
@@ -1202,17 +1211,20 @@ use 5.008001;
 			if (defined $table and length $table) {
 					push @search, 'c.relname ' . ($table =~ /[_%]/ ? 'LIKE ' : '= ') . $dbh->quote($table);
 			}
-			## All we can see is "table" or "view". Default is both
-			my $typesearch = q{IN ('r','v')};
-			if (defined $type and length $type) {
-				if ($type =~ /\btable\b/i and $type !~ /\bview\b/i) {
-					$typesearch = q{= 'r'};
-				}
-				elsif ($type =~ /\bview\b/i and $type !~ /\btable\b/i) {
-					$typesearch = q{= 'v'};
-				}
-			}
-			push @search, "c.relkind $typesearch";
+
+      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  ~ '^pg_'},
+          'SYSTEM VIEW'  => q{c.relkind = 'v' AND n.nspname  ~ '^pg_'},
+      );
+			my $typespec = join ' OR ' =>
+			                 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) {
-- 
1.8.4