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