Hey all,
I was really happy to see the updates to Pg.pm. In the spirit of
renewed enthusiasm for this module I patched it so support the
table_type argument for table_info().
I was confused by the ORDER BY clause, I could only get it to work for
the unified table view case, but it seemed to make no difference.
Cheers,
jas.
--- /home/jasons/.cpan/build/DBD-Pg-1.12/Pg.pm~ Tue Apr 9 19:54:34 2002
+++ /home/jasons/.cpan/build/DBD-Pg-1.12/Pg.pm Sat Apr 13 00:29:57 2002
@@ -144,9 +144,8 @@
sub table_info { # DBI spec: TABLE_CAT, TABLE_SCHEM, TABLE_NAME,
TABLE_TYPE, REMARKS
- my($dbh) = @_;
-
- my $sth = $dbh->prepare(qq{
+ my($dbh,$cat,$schema,$name,$type) = @_;
+ my $table_sql = qq{
SELECT NULL::text AS "TABLE_CAT"
, u.usename AS "TABLE_SCHEM"
, c.relname AS "TABLE_NAME"
@@ -159,12 +158,13 @@
AND c.relname !~ '^pg_'
AND c.relname !~ '^xin[vx][0-9]+'
AND c.relowner = u.usesysid
- UNION
- SELECT NULL::text
- , u.usename
- , c.relname
- , 'VIEW'
- , d.description
+ };
+ my $view_sql = qq{
+ SELECT NULL::text AS "TABLE_CAT"
+ , u.usename AS "TABLE_SCHEM"
+ , c.relname AS "TABLE_NAME"
+ , 'VIEW' AS "TABLE_TYPE"
+ , d.description AS "REMARKS"
FROM pg_user u
, pg_class c LEFT OUTER JOIN pg_description AS d ON c.relfilenode =
d.objoid and d.objsubid = 0
WHERE c.relkind = 'v'
@@ -172,10 +172,19 @@
AND c.relname !~ '^pg_'
AND c.relname !~ '^xin[vx][0-9]+'
AND c.relowner = u.usesysid
- ORDER BY 2, 3, 4
- }) or return undef;
+ };
- $sth->execute();
+ my $sql;
+ if ($type eq 'TABLE') {
+ $sql = "$table_sql";
+ } elsif ($type eq 'VIEW') {
+ $sql = "$view_sql";
+ } else {
+ $sql = "$table_sql UNION $view_sql ORDER BY 2, 3, 4";
+ }
+ my $sth = $dbh->prepare($sql)
+ or return undef;
+ $sth->execute();
return $sth;
}
