Обсуждение: Doubt regarding query parameter metadata
Hello everyone,
I'm using the PQprepare and PQdescribePrepared functions of the libpq in order to retrieve query parameter metadata and encode certain values based on the types of columns I'm dealing with.
The issue I'm finding is that the Oid returned for a VARCHAR column is TEXTOID (25) instead of VARCHAROID (1043).
From what I read, a VARCHAR column with unspecified size behaves in the same way a TEXT column does, and I don't know if this could possibly affect the Oid of a column or not.
But that's not the case here: the VARCHAR column has a specified size of 20.
Here's the CREATE TABLE in question:
CREATE TABLE oid_test (
key CHAR(10),
value VARCHAR(20)
);
And the resulting data on pg_attribute table:
postgres=# select attname, attlen, atttypid, attcollation, atttypmod from pg_attribute where attrelid = 'oid_test'::regclass;
attname | attlen | atttypid | attcollation | atttypmod
----------+--------+----------+--------------+-----------
tableoid | 4 | 26 | 0 | -1
cmax | 4 | 29 | 0 | -1
xmax | 4 | 28 | 0 | -1
cmin | 4 | 29 | 0 | -1
xmin | 4 | 28 | 0 | -1
ctid | 6 | 27 | 0 | -1
key | -1 | 1042 | 100 | 14
value | -1 | 1043 | 100 | 24
(8 rows)
Now, when I run the following program (simplified for brevity), I get 25 (TEXTOID) as the Oid of the query param instead of the expected 1043 (VARCHAROID).
Could anyone help me understand why?
#include <libpq-fe.h>
int main() {
int n = 1;
PGconn* conn = PQconnectdb("postgresql://127.0.0.1:5432/postgres");
PGresult* prepared = PQprepare(conn,
"oid-test",
"SELECT key FROM oid_test WHERE value = $1",
n,
NULL);
PGresult* metadata = PQdescribePrepared(conn, "oid-test");
printf("%d\n", PQparamtype(metadata, 0));
PQfinish(conn);
}
I tried reading the source code of Postgresql and libpq to make some sense of it, but I'm a newbie and couldn't get to the bottom of things.
I appreciate any help, thanks in advance!
Giovani
Giovani Garcia <giovani.garcia@protonmail.com> writes:
> The issue I'm finding is that the Oid returned for a VARCHAR column is TEXTOID (25) instead of VARCHAROID (1043).
This isn't hugely surprising in general. varchar has no operations
of its own; PG treats it as sort of a domain over text. So if you
do anything at all to a varchar column, it'd be quite likely for
the column to be implicitly coerced to text within the query.
> Now, when I run the following program (simplified for brevity),
> "SELECT key FROM oid_test WHERE value = $1",
Is that the *exact* query you're issuing? I don't see anything
in that that would cause a coercion, but maybe you left out some
relevant detail.
regards, tom lane
Hello Tom, Thanks for taking the time. > Is that theexact query you're issuing? Yes, that's the exact query. You can reproduce the issue by creating the table and running the C program from my previous e-mail. ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Thursday, 1 April 2021 11:14, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Giovani Garcia giovani.garcia@protonmail.com writes: > > > The issue I'm finding is that the Oid returned for a VARCHAR column is TEXTOID (25) instead of VARCHAROID (1043). > > This isn't hugely surprising in general. varchar has no operations > of its own; PG treats it as sort of a domain over text. So if you > do anything at all to a varchar column, it'd be quite likely for > the column to be implicitly coerced to text within the query. > > > Now, when I run the following program (simplified for brevity), > > > "SELECT key FROM oid_test WHERE value = $1", > > Is that theexact query you're issuing? I don't see anything > in that that would cause a coercion, but maybe you left out some > relevant detail. > > regards, tom lane
Giovani Garcia <giovani.garcia@protonmail.com> writes:
>> Is that theexact query you're issuing?
> Yes, that's the exact query.
Oh ... after actually trying the program you sent, I realized you are
looking at the wrong thing, or at least asking about something other
than what I thought you were asking about. You are querying
PQparamtype(), that is, the type of the symbol $1, not the type of
the returned column "key" which is what I thought you meant. As I
explained before, varchar has no operators, particularly not equality;
so "value = $1" is interpreted using the text = text operator, making
it effectively "value::text = $1::text". Thus $1 is resolved as type
text.
regards, tom lane