The following bug has been logged online:

Bug reference: 1267
Logged by: Colin Chapman

Email address: c0linchapman (AT) btinternet (DOT) com

PostgreSQL version: 8.0 Beta

Operating system: fedora linux

Description: Suggest TEXTOID parameters be treated like UNKNOWNOID

Details:

postgresql-8.0.0beta2

JSP likes to transfer data in parameters as text. When putting values into
a int column you get exception

this can be demonstrated by.

create table d ( numb int );
insert into d values ( '1' );
insert into d values ( '2'::text );
drop table d;

--- ./parse_coerce.c 2004-08-29 06:06:44.000000000 +0100
+++ /usr/local/postgresql-8.0.0beta2/src/backend/parser/parse_coerce.c
2004-09-23 13:05:34.383199696 +0100
@@ -137,7 +137,7 @@
/* NB: we do NOT want a RelabelType here */
return node;
}
- if (inputTypeId == UNKNOWNOID && IsA(node, Const))
+ if ( ( ( ( inputTypeId == UNKNOWNOID) || (inputTypeId == TEXTOID ) )
&& IsA(node, Const))&& IsA(node, Const) )
{
/*
* Input is a string constant with previously undetermined
type.
@@ -197,7 +197,7 @@

return result;
}
- if (inputTypeId == UNKNOWNOID && IsA(node, Param) &&
+ if ( ( ( inputTypeId == UNKNOWNOID ) || ( inputTypeId == TEXTOID ) )
&& IsA(node, Param) &&
((Param *) node)->paramkind == PARAM_NUM &&
pstate != NULL && pstate->p_variableparams)
{
@@ -220,7 +220,7 @@

(errcode(ERRCODE_UNDEFINED_PARAMETER),
errmsg("there is no parameter $%d",
paramno)));

- if (toppstate->p_paramtypes[paramno - 1] == UNKNOWNOID)
+ if ( (toppstate->p_paramtypes[paramno - 1] == UNKNOWNOID) ||
(toppstate->p_paramtypes[paramno - 1] == TEXTOID ) )
{
/* We've successfully resolved the type */
toppstate->p_paramtypes[paramno - 1] = targetTypeId;
@@ -373,7 +373,7 @@
* If input is an untyped string constant, assume we can
convert
* it to anything.
*/
- if (inputTypeId == UNKNOWNOID)
+ if ( ( inputTypeId == UNKNOWNOID) || (inputTypeId ==
TEXTOID ) )
continue;

/*



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
No thank you. Fix your broken client code, rather than screwing up type
coercion behavior for everyone else. If you've explicitly specified
that something is TEXT, then the system should treat it as TEXT.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html