Jeff, I've attached two patches. Both involve the meta data returned by DBD::Pg.
Tom
*** /usr/local/src/.cpan/build/DBD-Pg-1.11/Pg.pm.orig Tue Mar 19 20:46:56 2002 --- Pg.pm Mon Apr 8 22:36:12 2002 *************** *** 146,169 **** sub table_info { # DBI spec: TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS my($dbh) = @_; ! my $sth = $dbh->prepare(" ! SELECT c.reltype, u.usename, c.relname, 'TABLE', '' ! FROM pg_class c, pg_user u ! WHERE c.relkind = 'r' ! AND c.relhasrules = FALSE ! AND c.relname !~ '^pg_' ! AND c.relname !~ '^xin[vx][0-9]+' ! AND c.relowner = u.usesysid UNION ! SELECT c.reltype, u.usename, c.relname, 'VIEW', '' ! FROM pg_class c, pg_user u ! WHERE c.relkind = 'r' AND c.relhasrules = TRUE AND c.relname !~ '^pg_' AND c.relname !~ '^xin[vx][0-9]+' AND c.relowner = u.usesysid ! ORDER BY 1, 2, 3 ! ") or return undef; $sth->execute or return undef; $sth; --- 146,186 ---- sub table_info { # DBI spec: TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS my($dbh) = @_; ! my $sth = $dbh->prepare(qq{ ! SELECT ! NULL::text AS "TABLE_CAT" ! , u.usename AS "TABLE_SCHEM" ! , c.relname AS "TABLE_NAME" ! , 'TABLE' 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 = 'r' ! AND c.relhasrules = FALSE ! 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 ! 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' AND c.relhasrules = TRUE 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 or return undef; $sth; *************** *** 174,180 **** my($dbh) = @_; my $sth = $dbh->prepare(" ! select relname from pg_class where relkind = 'r' and relname !~ '^pg_' --- 191,197 ---- my($dbh) = @_; my $sth = $dbh->prepare(" ! select relname AS \"TABLE_NAME\" from pg_class where relkind = 'r' and relname !~ '^pg_' *************** *** 264,286 **** sub type_info_all { my ($dbh) = @_; ! my $names = { ! TYPE_NAME => 0, ! DATA_TYPE => 1, ! PRECISION => 2, ! LITERAL_PREFIX => 3, ! LITERAL_SUFFIX => 4, ! CREATE_PARAMS => 5, ! NULLABLE => 6, ! CASE_SENSITIVE => 7, ! SEARCHABLE => 8, ! UNSIGNED_ATTRIBUTE => 9, ! MONEY =>10, ! AUTO_INCREMENT =>11, ! LOCAL_TYPE_NAME =>12, ! MINIMUM_SCALE =>13, ! MAXIMUM_SCALE =>14, ! }; # typname |typlen|typprtlen| SQL92 # --------------+------+---------+ ------- --- 281,324 ---- sub type_info_all { my ($dbh) = @_; ! ! #my $names = { ! # TYPE_NAME => 0, ! # DATA_TYPE => 1, ! # PRECISION => 2, ! # LITERAL_PREFIX => 3, ! # LITERAL_SUFFIX => 4, ! # CREATE_PARAMS => 5, ! # NULLABLE => 6, ! # CASE_SENSITIVE => 7, ! # SEARCHABLE => 8, ! # UNSIGNED_ATTRIBUTE => 9, ! # MONEY =>10, ! # AUTO_INCREMENT =>11, ! # LOCAL_TYPE_NAME =>12, ! # MINIMUM_SCALE =>13, ! # MAXIMUM_SCALE =>14, ! # }; ! ! my $names = ! { TYPE_NAME => 0, ! DATA_TYPE => 1, ! COLUMN_SIZE => 2, # was PRECISION originally ! LITERAL_PREFIX => 3, ! LITERAL_SUFFIX => 4, ! CREATE_PARAMS => 5, ! NULLABLE => 6, ! CASE_SENSITIVE => 7, ! SEARCHABLE => 8, ! UNSIGNED_ATTRIBUTE=> 9, ! FIXED_PREC_SCALE => 10, # was MONEY originally ! AUTO_UNIQUE_VALUE => 11, # was AUTO_INCREMENT originally ! LOCAL_TYPE_NAME => 12, ! MINIMUM_SCALE => 13, ! MAXIMUM_SCALE => 14, ! NUM_PREC_RADIX => 15, ! }; ! # typname |typlen|typprtlen| SQL92 # --------------+------+---------+ ------- *************** *** 324,348 **** $names, # name type prec prefix suffix create params null case se unsign mon incr local min max # ! [ 'bytea', -2, 4096, '\'', '\'', undef, 1, '1', 3, undef, '0', '0', 'BYTEA', undef, undef ], ! [ 'bool', 0, 1, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'BOOLEAN', undef, undef ], ! [ 'int8', 8, 20, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'LONGINT', undef, undef ], ! [ 'int2', 5, 5, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'SMALLINT', undef, undef ], ! [ 'int4', 4, 10, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'INTEGER', undef, undef ], ! [ 'text', 12, 4096, '\'', '\'', undef, 1, '1', 3, undef, '0', '0', 'TEXT', undef, undef ], ! [ 'float4', 6, 12, undef, undef, 'precision', 1, '0', 2, '0', '0', '0', 'FLOAT', undef, undef ], ! [ 'float8', 7, 24, undef, undef, 'precision', 1, '0', 2, '0', '0', '0', 'REAL', undef, undef ], ! [ 'abstime', 10, 20, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'ABSTIME', undef, undef ], ! [ 'reltime', 10, 20, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'RELTIME', undef, undef ], ! [ 'tinterval', 11, 47, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'TINTERVAL', undef, undef ], ! [ 'money', 0, 24, undef, undef, undef, 1, '0', 2, undef, '1', '0', 'MONEY', undef, undef ], ! [ 'bpchar', 12, 4096, '\'', '\'', 'max length', 1, '1', 3, undef, '0', '0', 'CHARACTER', undef, undef ], ! [ 'varchar', 12, 4096, '\'', '\'', 'max length', 1, '1', 3, undef, '0', '0', 'VARCHAR', undef, undef ], ! [ 'date', 9, 10, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'DATE', undef, undef ], ! [ 'time', 10, 16, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'TIME', undef, undef ], ! [ 'datetime', 11, 47, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'DATETIME', undef, undef ], ! [ 'timespan', 11, 47, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'INTERVAL', undef, undef ], ! [ 'timestamp', 10, 19, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'TIMESTAMP', undef, undef ] # # intentionally omitted: char, all geometric types, all array types ]; --- 362,387 ---- $names, # name type prec prefix suffix create params null case se unsign mon incr local min max # ! [ 'bytea', -2, 4096, '\'', '\'', undef, 1, '1', 3, undef, '0', '0', 'BYTEA', undef, undef, undef ], ! [ 'bool', 0, 1, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'BOOLEAN', undef, undef, undef ], ! [ 'int8', 8, 20, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'LONGINT', undef, undef, undef ], ! [ 'int2', 5, 5, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'SMALLINT', undef, undef, undef ], ! [ 'int4', 4, 10, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'INTEGER', undef, undef, undef ], ! [ 'text', 12, 4096, '\'', '\'', undef, 1, '1', 3, undef, '0', '0', 'TEXT', undef, undef, undef ], ! [ 'float4', 6, 12, undef, undef, 'precision', 1, '0', 2, '0', '0', '0', 'FLOAT', undef, undef, undef ], ! [ 'float8', 7, 24, undef, undef, 'precision', 1, '0', 2, '0', '0', '0', 'REAL', undef, undef, undef ], ! [ 'abstime', 10, 20, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'ABSTIME', undef, undef, undef ], ! [ 'reltime', 10, 20, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'RELTIME', undef, undef, undef ], ! [ 'tinterval', 11, 47, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'TINTERVAL', undef, undef, undef ], ! [ 'money', 0, 24, undef, undef, undef, 1, '0', 2, undef, '1', '0', 'MONEY', undef, undef, undef ], ! [ 'bpchar', 1, 4096, '\'', '\'', 'max length', 1, '1', 3, undef, '0', '0', 'CHARACTER', undef, undef, undef ], ! [ 'bpchar', 12, 4096, '\'', '\'', 'max length', 1, '1', 3, undef, '0', '0', 'CHARACTER', undef, undef, undef ], ! [ 'varchar', 12, 4096, '\'', '\'', 'max length', 1, '1', 3, undef, '0', '0', 'VARCHAR', undef, undef, undef ], ! [ 'date', 9, 10, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'DATE', undef, undef, undef ], ! [ 'time', 10, 16, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'TIME', undef, undef, undef ], ! [ 'datetime', 11, 47, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'DATETIME', undef, undef, undef ], ! [ 'timespan', 11, 47, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'INTERVAL', undef, undef, undef ], ! [ 'timestamp', 10, 19, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'TIMESTAMP', undef, undef, undef ] # # intentionally omitted: char, all geometric types, all array types ];
*** /usr/local/src/.cpan/build/DBD-Pg-1.11/dbdimp.c.orig Tue Mar 19 21:30:25 2002 --- dbdimp.c Fri Apr 5 17:32:55 2002 *************** *** 794,805 **** return 17; /* bytea */ default: if (DBIc_WARN(imp_sth) && imp_sth && name) { ! warn("SQL type %d for '%s' is not fully supported, bound as VARCHAR instead"); } return pg_sql_type(imp_sth, name, SQL_VARCHAR); } } static int dbd_rebind_ph (sth, imp_sth, phs) --- 794,845 ---- return 17; /* bytea */ default: if (DBIc_WARN(imp_sth) && imp_sth && name) { ! warn("SQL type %d for '%s' is not fully supported, bound as VARCHAR instead", ! sql_type, name); } return pg_sql_type(imp_sth, name, SQL_VARCHAR); } } + static int + sql_pg_type (imp_sth, name, sql_type) + imp_sth_t *imp_sth; + char *name; + int sql_type; + { + if (dbis->debug >= 1) { + PerlIO_printf(DBILOGFP, "sql_pg_type name '%s' type '%d'\n", name, +sql_type ); + } + + switch (sql_type) { + case 17: /* bytea */ + return SQL_BINARY; + case 20: /* int8 */ + return SQL_DOUBLE; + case 21: /* int2 */ + return SQL_SMALLINT; + case 23: /* int4 */ + return SQL_INTEGER; + case 700: /* float4 */ + return SQL_NUMERIC; + case 701: /* float8 */ + return SQL_REAL; + case 1042: /* bpchar */ + return SQL_CHAR; + case 1043: /* varchar */ + return SQL_VARCHAR; + case 1082: /* date */ + return SQL_DATE; + case 1083: /* time */ + return SQL_TIME; + case 1296: /* date */ + return SQL_TIMESTAMP; + + default: + return sql_type; + } + } + static int dbd_rebind_ph (sth, imp_sth, phs) *************** *** 1504,1510 **** { STRLEN kl; char *key = SvPV(keysv,kl); ! int i; SV *retsv = Nullsv; if (dbis->debug >= 1) { PerlIO_printf(DBILOGFP, "dbd_st_FETCH\n"); } --- 1544,1550 ---- { STRLEN kl; char *key = SvPV(keysv,kl); ! int i, sz; SV *retsv = Nullsv; if (dbis->debug >= 1) { PerlIO_printf(DBILOGFP, "dbd_st_FETCH\n"); } *************** *** 1522,1537 **** av_store(av, i, newSVpv(PQfname(imp_sth->result, i),0)); } } else if ( kl== 4 && strEQ(key, "TYPE")) { AV *av = newAV(); retsv = newRV(sv_2mortal((SV*)av)); while(--i >= 0) { ! av_store(av, i, newSViv(PQftype(imp_sth->result, i))); ! } } else if (kl==9 && strEQ(key, "PRECISION")) { AV *av = newAV(); retsv = newRV(sv_2mortal((SV*)av)); while(--i >= 0) { ! av_store(av, i, &sv_undef); } } else if (kl==5 && strEQ(key, "SCALE")) { AV *av = newAV(); --- 1562,1581 ---- av_store(av, i, newSVpv(PQfname(imp_sth->result, i),0)); } } else if ( kl== 4 && strEQ(key, "TYPE")) { + /* Need to convert the Pg type to ANSI/SQL type. */ AV *av = newAV(); retsv = newRV(sv_2mortal((SV*)av)); while(--i >= 0) { ! av_store(av, i, newSViv(sql_pg_type( imp_sth, ! PQfname(imp_sth->result, i), ! PQftype(imp_sth->result, i)))); ! } } else if (kl==9 && strEQ(key, "PRECISION")) { AV *av = newAV(); retsv = newRV(sv_2mortal((SV*)av)); while(--i >= 0) { ! sz = PQfsize(imp_sth->result, i); ! av_store(av, i, sz > 0 ? newSViv(sz) : &sv_undef); } } else if (kl==5 && strEQ(key, "SCALE")) { AV *av = newAV(); *************** *** 1547,1552 **** --- 1591,1598 ---- } } else if (kl==10 && strEQ(key, "CursorName")) { retsv = &sv_undef; + } else if (kl==11 && strEQ(key, "RowsInCache")) { + retsv = &sv_undef; } else if (kl==7 && strEQ(key, "pg_size")) { AV *av = newAV(); retsv = newRV(sv_2mortal((SV*)av));