Re: Problems with PQfmod() returning -1 on varchar f

Bruce,

Thanks for your quick response. The column in question is defined as 'DATA
VARCHAR(20) NOT NULL'. The issue occurs when this is referenced in a fairly
complex query, such as:

SELECT data,int16_col,int64_col,float32_col,float64_col,
date_col,row_id
FROM
(SELECT b.data,b.int16_col,b.int64_col,
b.float3_col,b.float64_col,b.date_col,b.row_id
FROM table1 b
LEFT JOIN
(SELECT *
FROM table3
) d ON b.ROW_ID = d.ROW_ID
WHERE d.ROW_ID IS NULL
UNION ALL
SELECT a.data,a.int16_col,a.int64_col,a.float32_col,
a.float6_col,a.date_col,a.row_id
FROM table2 a
LEFT JOIN
(SELECT *
FROM table3
) d ON (a.ROW_ID = d.ROW_ID)
WHERE d.ROW_ID IS NULL
) V__282 LIMIT 0;

Where:

CREATE TABLE table1
(
data varchar(20) NOT NULL,
int16_col int2,
int64_col int8,
float32_col float4,
float64_col float8,
date_col timestamp,
row_id int4 NOT NULL,
CONSTRAINT table1_pk PRIMARY KEY (row_id)
)
WITHOUT OIDS;

CREATE TABLE table2
(
data varchar(20) NOT NULL,
int16_col int2,
int64_col int8,
float32_col float4,
float64_col float8,
date_col timestamp,
row_id int4 NOT NULL,
CONSTRAINT table2_pk PRIMARY KEY (row_id)
)
WITHOUT OIDS;

CREATE TABLE table3
(
row_id int8 NOT NULL,
int32_col int4 NOT NULL,
int64_col int8 NOT NULL,
CONSTRAINT table3_pk PRIMARY KEY (row_id)
)
WITHOUT OIDS;

In this particular case, I can go back and describe each of the referenced
tables, but there are other times in our application where this is not
practical. Can you recommend a more reliable method to describe such
queries? Is this a current limitation in Postgres which may be addressed at
a future release?

Thanks,

Jerry

_|_|
_|_| ESRI
Jerry L. Day
Software Developer
(909)793-2853 Ext. 1477
jerry_day(at)esri(dot)com

-----Original Message-----
From: Bruce Momjian [mailto:pgman(at)candle(dot)pha(dot)pa(dot)us]
Sent: Saturday, April 02, 2005 5:27 PM
To: Jerry Day
Cc: 'pgsql-interfaces(at)postgresql(dot)org'
Subject: Re: [INTERFACES] Problems with PQfmod() returning -1 on varchar
field (libpq-8.0.0 )?

Jerry Day wrote:
> Hi all,
>
> I've recently encountered a situation where PQfmod() fails to return the
> defined length of a varchar field referenced in a query, as expected -
> returning no information (-1), instead. Has anyone else encountered this
> issue? If so, why does this occur? Can you recommended a method to more
> reliably obtain the length of varchar fields, as defined in the relations
> referenced by a query?

Can we see the query? I suspect the column is being manipulated in some
way and the length isn't carried through to the query output --- if so,
there might not be much we can do about it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073