Hey Merlin,
Thank you for explanation !
Yes, I understand that specifying NULL instead real OID will provoke
the parser attempts to infer the data types in the same way as it would
do for untyped literal string constants.
But there are three string types: text, varchar(n) and character(n) which
has a different OIDs but they are all in the same type category. So, is it
worth it to implement some Varchar and Character types (which actually
wraps Text) at the library level or specifying the OID of text for contexts
where these parameters actually varchar or char (i.e. types of same
category) are safe?
2010/12/10 Merlin Moncure
<mmoncure@gmail.com>On Thu, Dec 9, 2010 at 1:24 PM, Dmitriy Igrishin <
dmitigr@gmail.com> wrote:
> Hey general@,
>
> To be assured and just for calmness.
>
> Problem:
>
> 1. CREATE TABLE test_tab (id integer, dat varchar(64));
>
> 2. INSERT INTO test_tab VALUES($1, $2) via PQexecParams,
> where paramTypes[0] == OID of bigint,
> paramTypes[1] == OID of text.
>
> Questions:
>
> Whether this case falls to
>
http://www.postgresql.org/docs/9.0/static/typeconv-query.html ?
>
> Is such cases safe or it is recommended (best) to specify a
> OIDs which are exact matches ?
The oid vector passed to the database in these functions is for
describing the data you are passing. If left NULL, you leave it up to
the database to try and guess what you are sending based on the
context of the query. This has pros and cons. With the text
protocol, it's somewhat ok to leave off the oid vector: this isn't
much different from sending uncasted unknown strings into psql. It's
basically there to protect you from sending bogus data to the server
and reduce chance of type confusion. If you are using binary
protocol, the oid vector is absolutely essential -- it's insane to
have the server 'guess' what you are passing in since a wrong guess
could be interpreted improperly vs a formatting error that text
casting raises. If you are wrapping libpq with a higher level
library, sending the correct oids always would be a pretty good idea.
Meaning, you should try and coerce your application/language types
into a type the database understands and pass a corresponding oid.
merlin
--
// Dmitriy.
