[DBD::Pg 08/14] LOCAL TEMPORARY support

Committed by pilcrow <[email protected]>

Subject: [DBD::Pg 08/14] LOCAL TEMPORARY support

---
 Pg.pm | 35 ++++++++++++++++++++++++-----------
 1 file changed, 24 insertions(+), 11 deletions(-)

diff --git a/Pg.pm b/Pg.pm
index 80b9128..507238b 100644
--- a/Pg.pm
+++ b/Pg.pm
@@ -1170,7 +1170,7 @@ use 5.008001;
 					 and (defined $table and $table eq '')
 					 and (defined $type and $type eq '%')
 					) {
-			$tbl_sql = qq{
+			$tbl_sql = q{
                     SELECT "TABLE_CAT"
                          , "TABLE_SCHEM"
                          , "TABLE_NAME"
@@ -1185,13 +1185,16 @@ use 5.008001;
                             , 'relkind: r'   AS "REMARKS"
                        UNION
                        SELECT 'SYSTEM TABLE'
-                            , 'relkind: r; nspname ~ ^pg_'
+                            , 'relkind: r; nspname ~ ^pg_(catalog|toast)$'
                        UNION
                        SELECT 'VIEW'
                             , 'relkind: v'
                        UNION
                        SELECT 'SYSTEM VIEW'
-                            , 'relkind: v; nspname ~ ^pg_') type_info
+                            , 'relkind: v; nspname ~ ^pg_(catalog|toast)$'
+                       UNION
+                       SELECT 'LOCAL TEMPORARY'
+                            , 'relkind: r; nspname ~ ^pg_(toast_)?temp') type_info
                      ORDER BY "TABLE_TYPE" ASC
                 };
 		}
@@ -1213,10 +1216,11 @@ use 5.008001;
 			}
 
 			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_'},
+			    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}
@@ -1236,11 +1240,18 @@ 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) ~ '^pg_' THEN 'SYSTEM VIEW' ELSE 'VIEW' END
+                     , 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) ~ '^pg_' THEN 'SYSTEM TABLE' ELSE 'TABLE' END
+                                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"
                      , d.description AS "REMARKS" $showtablespace $extracols
                 FROM pg_catalog.pg_class AS c
@@ -1249,6 +1260,8 @@ use 5.008001;
                     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"
                 };
 		}
-- 
1.8.4