Bug #34351 for DBD-Pg: [PATCH] add pg_enum_values to column_info data

I've attached a patch which adds a new Pg-specific key for the column_info data, "pg_enum_values". It is an array reference of valid enum values for enum-typed columns. The patch should be a no-op with pre-8.3 versions Postgres, but I don't have an earlier version to test with.

Only in .: dbd-pg-enum-values.diff diff -ru ../DBD-Pg-2.4.0/Pg.pm ./Pg.pm --- ../DBD-Pg-2.4.0/Pg.pm 2008-03-21 11:38:54.000000000 -0500 +++ ./Pg.pm 2008-03-22 12:15:38.000000000 -0500 @@ -443,6 +443,8 @@ , a.attrelid AS "pg_attrelid" , a.attnum AS "pg_attnum" , a.atttypmod AS "pg_atttypmod" + , t.typtype AS "_pg_type_typtype" + , t.oid AS "_pg_type_oid" FROM pg_catalog.pg_type t JOIN pg_catalog.pg_attribute a ON (t.oid = a.atttypid) @@ -485,11 +487,14 @@ pg_schema 20 pg_table 21 pg_column 22 + pg_enum_values 23 /); my $oldconstraint_sth; for my $row (@$data) { + my $typoid = pop @$row; + my $typtype = pop @$row; my $typmod = pop @$row; my $attnum = pop @$row; my $aid = pop @$row; @@ -498,9 +503,7 @@ _calc_col_size($typmod,$row->[$col_map{COLUMN_SIZE}]); # Replace the Pg type with the SQL_ type - my $w = $row->[$col_map{DATA_TYPE}]; $row->[$col_map{DATA_TYPE}] = DBD::Pg::db::pg_type_info($dbh,$row->[$col_map{DATA_TYPE}]); - $w = $row->[$col_map{DATA_TYPE}]; # Add pg_constraint my $SQL = "SELECT consrc FROM pg_catalog.pg_constraint WHERE contype = 'c' AND ". @@ -512,11 +515,17 @@ else { $row->[19] = undef; } - $col_map{pg_constraint} = 19; - } - # get rid of atttypmod that we no longer need - delete $col_map{pg_atttypmod}; + if ( $typtype eq 'e' ) { + my $SQL = "SELECT enumlabel FROM pg_catalog.pg_enum WHERE enumtypid = $typoid" . + " ORDER BY oid"; + $row->[23] = $dbh->selectcol_arrayref($SQL); + } + else { + $row->[23] = undef; + } + + } # Since we've processed the data in Perl, we have to jump through a hoop # To turn it back into a statement handle @@ -2620,13 +2629,14 @@ SQL_DATETIME_SUB CHAR_OCTET_LENGTH -Also, five additional non-standard fields are returned: +Also, six additional non-standard fields are returned: pg_type - data type with additional info i.e. "character varying(20)" pg_constraint - holds column constraint definition pg_schema - the unquoted name of the schema pg_table - the unquoted name of the table pg_column - the unquoted name of the column + pg_enum_values - an array reference of allowed values for an enum column Note that the TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME fields all return output wrapped in quote_ident(). If you need the unquoted version, use diff -ru ../DBD-Pg-2.4.0/t/03dbmethod.t ./t/03dbmethod.t --- ../DBD-Pg-2.4.0/t/03dbmethod.t 2008-03-21 11:22:40.000000000 -0500 +++ ./t/03dbmethod.t 2008-03-22 12:12:19.000000000 -0500 @@ -25,7 +25,7 @@ if (! defined $dbh) { plan skip_all => 'Connection to database failed, cannot continue testing'; } -plan tests => 216; +plan tests => 218; isnt( $dbh, undef, 'Connect to database for database handle method testing'); @@ -490,6 +490,24 @@ $t = q{DB handle method "column_info" works with non-lowercased columns}; is( $result->{COLUMN_NAME}, q{"CaseTest"}, $t); +SKIP: +{ + skip 'Enum tests require Pg 8.3.0+', 2 + unless $dbh->{pg_server_version} >= 80300; + + { + local $dbh->{Warn} = 0; + + $dbh->do( q{CREATE TYPE dbd_pg_enumerated AS ENUM ('foo', 'bar', 'baz', 'buz')} ); + $dbh->do( q{CREATE TABLE dbd_pg_enum_test ( is_enum dbd_pg_enumerated NOT NULL )} ); + } + + $sth = $dbh->column_info('','','dbd_pg_enum_test','is_enum'); + $result = $sth->fetchall_arrayref({})->[0]; + is( $result->{pg_type}, 'dbd_pg_enumerated', 'DB handle method "column_info" returns proper pg_type'); + is_deeply( $result->{pg_enum_values}, [ qw( foo bar baz buz ) ], + 'DB handle method "column_info" returns proper pg_enum_values'); +} # # Test of the "primary_key_info" database handle method diff -ru ../DBD-Pg-2.4.0/t/dbdpg_test_setup.pl ./t/dbdpg_test_setup.pl --- ../DBD-Pg-2.4.0/t/dbdpg_test_setup.pl 2008-02-10 12:11:35.000000000 -0600 +++ ./t/dbdpg_test_setup.pl 2008-03-22 12:16:20.000000000 -0500 @@ -23,6 +23,7 @@ 'dbd_pg_test2', 'dbd_pg_test1', 'dbd_pg_test', + 'dbd_pg_enum_test', ); my @sequences = @@ -176,6 +177,9 @@ $dbh->rollback() if ! $dbh->{AutoCommit}; + ## Shuts up warnings about cascading + local $dbh->{Warn} = 0; + for my $name (@tables) { my $schema = ($name =~ s/(.+)\.(.+)/$2/) ? $1 : $S; next if ! relation_exists($dbh,$schema,$name);