On Mon, 2010-02-08 at 20:50 +0100, Florian Weimer wrote: > I saw your note that you have to specify the types for date values > etc. Is this really desirable or even necessary? Can't you specify > the type as unknown (OID 705, I believe)?
I believe the problem that Andrew is describing is that: SELECT $1 + 1; will infer that $1 is of type int4. But if you really intended $1 to be a date (which is also valid), it will cause a problem. If the date is passed in text format, it will cause an error in int4in(), because the text representation of a date isn't a valid text representation for an integer. If the date is passed in binary format, it will pass it to int4recv() -- but because the date is 4 bytes, and int4recv is defined for any 4-byte input, it won't cause an error; it will produce a wrong result. In other words, the binary representation for a date _is_ a valid binary representation for an integer. The type inference has found the wrong type, but the recv function still accepts it, which causes a problem. The solution is to write the query in an unambiguous way: SELECT $1::date + 1; which is good practice, anyway. If it's not obvious to the type inference system, it's probably not obvious to you, and will probably surprise you ;) Or, as Andrew suggests, you can pass the type oid along with the parameter so that postgresql knows the right type. Either way, relying on a type input or a recv function to cause a type error is much more fragile. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers