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));
