Libpq: PQftype, PQfsize

"Bozena Potempa" <Bozena.Potempa@otc.pl> writes:

I have a test table with varchar(40) column. After executing the following
query:
select substr(fc,1,2) from test
PQftype returns for the result column PG_TYPE_TEXT and PQfsize returns -1.
Is it the expected behaviour?

Yes. substr() returns text. But even if it returned varchar, you'd
probably get -1 for the fsize. PG does not make any attempt to predict
the result width of functions.

regards, tom lane

From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

[..]

"Bozena Potempa" <Bozena.Potempa@otc.pl> writes:

I have a test table with varchar(40) column. After executing the
following
query:
select substr(fc,1,2) from test
PQftype returns for the result column PG_TYPE_TEXT and

PQfsize returns -1.

Is it the expected behaviour?

Yes. substr() returns text. But even if it returned varchar,
you'd probably get -1 for the fsize. PG does not make any
attempt to predict the result width of functions.

Thank you. In this case (substring) there is no much to predict, just a
simple calculation, but I understand that it is a part of larger and more
complicated functionality. I tried to find a workaround with a type cast:
select substr(fc,1,2)::varchar(2) from test
Now the type returned is varchar, but the size is still -1. I think that it
is not a correct return: the size is specified explicitly in the query and
could be used by PQfsize.

Bozena

"Bozena Potempa" <Bozena.Potempa@otc.pl> writes:

Thank you. In this case (substring) there is no much to predict, just a
simple calculation, but I understand that it is a part of larger and more
complicated functionality. I tried to find a workaround with a type cast:
select substr(fc,1,2)::varchar(2) from test
Now the type returned is varchar, but the size is still -1. I think that it
is not a correct return: the size is specified explicitly in the query and
could be used by PQfsize.

Oh ... actually the problem there is that you have the wrong idea about
what PQfsize means. What that returns is pg_type.typlen for the data
type, which is always going to be -1 for a varlena type like varchar.

The thing that you need to look at if you want to see information like
the max length of a varchar is typmod (PQfmod). The typmod generally
has some funny datatype-specific encoding; for varchar and char it
works like this:
-1: max length unknown or unspecified
n>0: max length is n-4 characters

regards, tom lane

From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

Thank you. In this case (substring) there is no much to

predict, just

a simple calculation, but I understand that it is a part of

larger and

more complicated functionality. I tried to find a workaround

with a type cast:

select substr(fc,1,2)::varchar(2) from test Now the type returned is
varchar, but the size is still -1. I think that it is not a correct
return: the size is specified explicitly in the query and could be
used by PQfsize.

Oh ... actually the problem there is that you have the wrong
idea about what PQfsize means. What that returns is
pg_type.typlen for the data type, which is always going to be
-1 for a varlena type like varchar.

The thing that you need to look at if you want to see
information like the max length of a varchar is typmod
(PQfmod). The typmod generally has some funny
datatype-specific encoding; for varchar and char it works like this:
-1: max length unknown or unspecified
n>0: max length is n-4 characters

Thank you very much Tom. PQfmode returns the correct value when using a type
cast, so it solves my current problem.
Perhaps you will implement the exact column size for querries with character
functions somwhere in the future. It is a nice feature, which is implemented
by Oracle or MS SQL Server. Do not know about MySQL.

Regards,
Bozena Potempa