Committed by Greg Sabino Mullane <[email protected]>
Add a new field, pg_expression, to the results from
statistics_info. This will show the index expression for the column in
question. In other words, things such as lower(foo) will show up here. Per
request from RT #76608
---
Pg.pm | 76 ++++++++++++++++++++++++++++++++++++++++------------------
t/03dbmethod.t | 43 +++++++++++++++++----------------
2 files changed, 76 insertions(+), 43 deletions(-)
diff --git a/Pg.pm b/Pg.pm
index a6ee67f..3a23ded 100644
--- a/Pg.pm
+++ b/Pg.pm
@@ -611,8 +611,9 @@ use 5.008001;
my $stats_sql = qq{
SELECT
c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname,
- n.nspname, c.relpages, c.reltuples, i.indexprs,
- pg_get_expr(i.indpred,i.indrelid) as predicate
+ n.nspname, c.relpages, c.reltuples, i.indexprs, i.indnatts,
i.indexrelid,
+ pg_get_expr(i.indpred,i.indrelid) as predicate,
+ pg_get_expr(i.indexprs,i.indrelid, true) AS indexdef
FROM
pg_catalog.pg_index i, pg_catalog.pg_class c,
pg_catalog.pg_class d, pg_catalog.pg_am a,
@@ -624,6 +625,14 @@ use 5.008001;
i.indisunique desc, a.amname, c.relname
};
+ my $indexdef_sql = qq{
+ SELECT
+ pg_get_indexdef(indexrelid,x,true)
+ FROM
+ pg_index
+ JOIN generate_series(1,?) s(x) ON indexrelid = ?
+ };
+
my @output_rows;
# Table-level stats
@@ -645,6 +654,7 @@ use 5.008001;
$tst->{reltuples},# CARDINALITY
$tst->{relpages}, # PAGES
undef, # FILTER_CONDITION
+ undef, # pg_expression
]);
}
@@ -653,16 +663,16 @@ use 5.008001;
$colnames_sth->execute(@exe_args) or return undef;
my $colnames = $colnames_sth->fetchall_hashref('attnum');
+ # Fetch the individual parts of the index
+ my $sth_indexdef = $dbh->prepare($indexdef_sql);
+
# Fetch the index definitions
my $sth = $dbh->prepare($stats_sql);
$sth->execute(@exe_args) or return undef;
STAT_ROW:
- #use Data::Dumper;
- #warn Dumper $stats_sql;
while (my $row = $sth->fetchrow_hashref) {
- #warn Dumper $row;
- next if $row->{indexprs}; # We can't return these
accurately via this interface ...
+
next if $unique_only and !$row->{indisunique};
my $indtype = $row->{indisclustered}
@@ -675,21 +685,26 @@ use 5.008001;
my $nonunique = $row->{indisunique} ? 0 : 1;
my @index_row = (
- undef, # TABLE_CAT
- $row->{nspname}, # TABLE_SCHEM
- $table, # TABLE_NAME
- $nonunique, # NON_UNIQUE
- undef, # INDEX_QUALIFIER
- $row->{relname}, # INDEX_NAME
- $indtype, # TYPE
- undef, # ORDINAL_POSITION
- undef, # COLUMN_NAME
- 'A', # ASC_OR_DESC
- $row->{reltuples}, # CARDINALITY
- $row->{relpages}, # PAGES
- $row->{predicate}, # FILTER_CONDITION
+ undef, # TABLE_CAT 0
+ $row->{nspname}, # TABLE_SCHEM 1
+ $table, # TABLE_NAME 2
+ $nonunique, # NON_UNIQUE 3
+ undef, # INDEX_QUALIFIER 4
+ $row->{relname}, # INDEX_NAME 5
+ $indtype, # TYPE 6
+ undef, # ORDINAL_POSITION 7
+ undef, # COLUMN_NAME 8
+ 'A', # ASC_OR_DESC 9
+ $row->{reltuples}, # CARDINALITY 10
+ $row->{relpages}, # PAGES 11
+ $row->{predicate}, # FILTER_CONDITION 12
+ undef, # pg_expression 13
);
+ ## Grab expression information
+ $sth_indexdef->execute($row->{indnatts},
$row->{indexrelid});
+ my $expression = $sth_indexdef->fetchall_arrayref();
+
my $col_nums = $row->{indkey};
$col_nums =~ s/^\s+//;
my @col_nums = split(/\s+/, $col_nums);
@@ -697,15 +712,17 @@ use 5.008001;
my $ord_pos = 1;
for my $col_num (@col_nums) {
my @copy = @index_row;
- $copy[7] = $ord_pos++; # ORDINAL_POSITION
+ $copy[7] = $ord_pos; # ORDINAL_POSITION
$copy[8] = $colnames->{$col_num}->{attname}; #
COLUMN_NAME
+ $copy[13] = $expression->[$ord_pos-1][0];
push(@output_rows, \@copy);
+ $ord_pos++;
}
}
my @output_colnames = qw/ TABLE_CAT TABLE_SCHEM TABLE_NAME
NON_UNIQUE INDEX_QUALIFIER
INDEX_NAME TYPE ORDINAL_POSITION
COLUMN_NAME ASC_OR_DESC
- CARDINALITY PAGES FILTER_CONDITION /;
+ CARDINALITY PAGES FILTER_CONDITION
pg_expression /;
return _prepare_from_data('statistics_info', \@output_rows,
\@output_colnames);
}
@@ -2983,11 +3000,24 @@ Returns a statement handle that can be fetched from to
give statistics informati
on a specific table and its indexes. The C<$table> argument is mandatory. The
C<$schema> argument is optional but recommended. The C<$unique_only> argument,
if true,
causes only information about unique indexes to be returned. The C<$quick>
argument is
-not used by DBD::Pg. For information on the format of the rows returned,
please see the DBI
-documentation.
+not used by DBD::Pg. For information on the format of the standard rows
returned, please
+see the DBI documentation.
=for html <a
href="http://search.cpan.org/~timb/DBI/DBI.pm#statistics_info";>DBI section on
statistics_info</a>
+In addition, the following Postgres specific columns are returned:
+
+=over 4
+
+=item pg_expression
+
+Postgres allows indexes on functions and scalar expressions based on one or
more columns. This field
+will always be populated if an index, but the lack of an entry in the
COLUMN_NAME should indicate
+that this is an index expression.
+
+=back
+
+
=head3 B<tables>
@names = $dbh->tables( undef, $schema, $table, $type, \%attr );
diff --git a/t/03dbmethod.t b/t/03dbmethod.t
index e86d0de..48b5378 100644
--- a/t/03dbmethod.t
+++ b/t/03dbmethod.t
@@ -688,8 +688,10 @@ is ($sth, undef, $t);
'CONSTRAINT dbd_pg_test1_pk PRIMARY KEY (a))');
$dbh->do("ALTER TABLE $table1 ADD CONSTRAINT dbd_pg_test1_uc1 UNIQUE
(b)");
$dbh->do("CREATE UNIQUE INDEX dbd_pg_test1_index_c ON $table1(c)");
+
$dbh->do("CREATE TABLE $table2 (a INT, b INT, c INT, PRIMARY KEY(a,b),
UNIQUE(b,c))");
- $dbh->do("CREATE INDEX dbd_pg_test2_skipme ON $table2(c,(a+b))");
+ $dbh->do("CREATE INDEX dbd_pg_test2_expr ON $table2(c,(a+b))");
+
$dbh->do("CREATE TABLE $table3 (a INT, b INT, c INT, PRIMARY KEY(a))
WITH OIDS");
$dbh->do("CREATE UNIQUE INDEX dbd_pg_test3_index_b ON $table3(b)");
$dbh->do("CREATE INDEX dbd_pg_test3_index_c ON $table3 USING hash(c)");
@@ -700,30 +702,32 @@ is ($sth, undef, $t);
my $correct_stats = {
one => [
- [ undef, $schema, $table1, undef, undef, undef, 'table', undef, undef,
undef, '0', '0', undef ],
- [ undef, $schema, $table1, '0', undef, 'dbd_pg_test1_index_c', 'btree',
1, 'c', 'A', '0', '1', undef ],
- [ undef, $schema, $table1, '0', undef, 'dbd_pg_test1_pk', 'btree',
1, 'a', 'A', '0', '1', undef ],
- [ undef, $schema, $table1, '0', undef, 'dbd_pg_test1_uc1', 'btree',
1, 'b', 'A', '0', '1', undef ],
+ [ undef, $schema, $table1, undef, undef, undef, 'table', undef, undef,
undef, '0', '0', undef, undef ],
+ [ undef, $schema, $table1, '0', undef, 'dbd_pg_test1_index_c', 'btree',
1, 'c', 'A', '0', '1', undef, 'c' ],
+ [ undef, $schema, $table1, '0', undef, 'dbd_pg_test1_pk', 'btree',
1, 'a', 'A', '0', '1', undef, 'a' ],
+ [ undef, $schema, $table1, '0', undef, 'dbd_pg_test1_uc1', 'btree',
1, 'b', 'A', '0', '1', undef, 'b' ],
],
two => [
- [ undef, $schema, $table2, undef, undef, undef, 'table', undef, undef,
undef, '0', '0', undef ],
- [ undef, $schema, $table2, '0', undef, 'dbd_pg_test2_b_key', 'btree',
1, 'b', 'A', '0', '1', undef ],
- [ undef, $schema, $table2, '0', undef, 'dbd_pg_test2_b_key', 'btree',
2, 'c', 'A', '0', '1', undef ],
- [ undef, $schema, $table2, '0', undef, 'dbd_pg_test2_pkey', 'btree',
1, 'a', 'A', '0', '1', undef ],
- [ undef, $schema, $table2, '0', undef, 'dbd_pg_test2_pkey', 'btree',
2, 'b', 'A', '0', '1', undef ],
+ [ undef, $schema, $table2, undef, undef, undef, 'table', undef, undef,
undef, '0', '0', undef, undef ],
+ [ undef, $schema, $table2, '0', undef, 'dbd_pg_test2_b_key', 'btree',
1, 'b', 'A', '0', '1', undef, 'b' ],
+ [ undef, $schema, $table2, '0', undef, 'dbd_pg_test2_b_key', 'btree',
2, 'c', 'A', '0', '1', undef, 'c' ],
+ [ undef, $schema, $table2, '0', undef, 'dbd_pg_test2_pkey', 'btree',
1, 'a', 'A', '0', '1', undef, 'a' ],
+ [ undef, $schema, $table2, '0', undef, 'dbd_pg_test2_pkey', 'btree',
2, 'b', 'A', '0', '1', undef, 'b' ],
+ [ undef, $schema, $table2, '1', undef, 'dbd_pg_test2_expr', 'btree',
1, 'c', 'A', '0', '1', undef, 'c' ],
+ [ undef, $schema, $table2, '1', undef, 'dbd_pg_test2_expr', 'btree',
2, undef, 'A', '0', '1', undef, '(a + b)' ],
],
three => [
- [ undef, $schema, $table3, undef, undef, undef, 'table', undef, undef,
undef, '0', '0', undef ],
- [ undef, $schema, $table3, '0', undef, 'dbd_pg_test3_index_b', 'btree',
1, 'b', 'A', '0', '1', undef ],
- [ undef, $schema, $table3, '0', undef, 'dbd_pg_test3_pkey', 'btree',
1, 'a', 'A', '0', '1', undef ],
- [ undef, $schema, $table3, '0', undef, 'dbd_pg_test3_pred', 'btree',
1, 'c', 'A', '0', '1', '((c > 0) AND (c < 45))' ],
- [ undef, $schema, $table3, '1', undef, 'dbd_pg_test3_oid', 'btree',
1, 'oid', 'A', '0', '1', undef ],
- [ undef, $schema, $table3, '1', undef, 'dbd_pg_test3_index_c',
'hashed', 1, 'c', 'A', '0', '4', undef ],
+ [ undef, $schema, $table3, undef, undef, undef, 'table', undef, undef,
undef, '0', '0', undef, undef ],
+ [ undef, $schema, $table3, '0', undef, 'dbd_pg_test3_index_b', 'btree',
1, 'b', 'A', '0', '1', undef, 'b' ],
+ [ undef, $schema, $table3, '0', undef, 'dbd_pg_test3_pkey', 'btree',
1, 'a', 'A', '0', '1', undef, 'a' ],
+ [ undef, $schema, $table3, '0', undef, 'dbd_pg_test3_pred', 'btree',
1, 'c', 'A', '0', '1', '((c > 0) AND (c < 45))', 'c' ],
+ [ undef, $schema, $table3, '1', undef, 'dbd_pg_test3_oid', 'btree',
1, 'oid', 'A', '0', '1', undef, 'oid' ],
+ [ undef, $schema, $table3, '1', undef, 'dbd_pg_test3_index_c',
'hashed', 1, 'c', 'A', '0', '4', undef, 'c' ],
],
three_uo => [
- [ undef, $schema, $table3, '0', undef, 'dbd_pg_test3_index_b', 'btree',
1, 'b', 'A', '0', '1', undef ],
- [ undef, $schema, $table3, '0', undef, 'dbd_pg_test3_pkey', 'btree',
1, 'a', 'A', '0', '1', undef ],
- [ undef, $schema, $table3, '0', undef, 'dbd_pg_test3_pred', 'btree',
1, 'c', 'A', '0', '1', '((c > 0) AND (c < 45))' ],
+ [ undef, $schema, $table3, '0', undef, 'dbd_pg_test3_index_b', 'btree',
1, 'b', 'A', '0', '1', undef, 'b' ],
+ [ undef, $schema, $table3, '0', undef, 'dbd_pg_test3_pkey', 'btree',
1, 'a', 'A', '0', '1', undef, 'a' ],
+ [ undef, $schema, $table3, '0', undef, 'dbd_pg_test3_pred', 'btree',
1, 'c', 'A', '0', '1', '((c > 0) AND (c < 45))', 'c' ],
],
};
@@ -788,7 +792,6 @@ $dbh->do("DROP TABLE $table1");
} ## end of statistics_info tests
-
#
# Test of the "foreign_key_info" database handle method
#
--
1.8.4
