Обсуждение: 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.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 unspecifiedn>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