Re: Libpq: PQftype, PQfsize

Hi,

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? The most suprising for me is PQfsize.
Tested on PostgreSQL 8.4, 32-bit Windows.
Thank you in advance for explanations.

Bozena

"Bozena Potempa" <Bozena(dot)Potempa(at)otc(dot)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(at)sss(dot)pgh(dot)pa(dot)us]
[..]
>"Bozena Potempa" <Bozena(dot)Potempa(at)otc(dot)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(dot)Potempa(at)otc(dot)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(at)sss(dot)pgh(dot)pa(dot)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