Subqueries in PostGreSql

[rc]

With a postgresql database, When I load in a TUniQuery a query containing a subquery, this by example :

Code: Select all

SELECT LOCAL.*,
(SELECT N_COMPTE FROM COMPTE WHERE ID_COMPTE = LOCAL.ID_COMPTE) as N_COMPTE
FROM LOCAL

it interprets N_COMPTE field as a TMemoField instead of a TStringField, which causes a lot a problem of application compatibility between DBMS.
Is this a known problem of your postgresql provider, or is there another explication ?

Thank you for your answer

[bork]

Hello

Please send us the DDL script to create the COMPTE and LOCAL tables.

[rc]

Here it is :

Code: Select all

/* Table: COMPTE */
CREATE TABLE COMPTE (ID_COMPTE SERIAL NOT NULL,
  N_COMMUNE INTEGER NOT NULL,
  N_COMPTE VARCHAR(6) NOT NULL,
CONSTRAINT COMPTE_UNIQUE UNIQUE (N_COMMUNE, N_COMPTE),
CONSTRAINT COMPTE_PK PRIMARY KEY (ID_COMPTE));

/* Table: LOCAL */
CREATE TABLE LOCAL (ID_LOCAL SERIAL NOT NULL,
  N_COMMUNE INTEGER NOT NULL,
  N_INVARIANT VARCHAR(10) NOT NULL,
  ID_PARCELLE INTEGER NOT NULL,
  ID_COMPTE INTEGER,
CONSTRAINT LOCAL_PK PRIMARY KEY (ID_LOCAL),
CONSTRAINT LOCAL_UNIQUE UNIQUE (N_COMMUNE, N_INVARIANT));

[bork]

PgDAC create fields with types depending on the types that PostgreSQL server returns. PostgreSQL server before version 8.4 returns type that is eqal to Memo and PgDAC creates TMemoField. PostgreSQL server version 8.4 returns type that is eqal to String and PgDAC creates TStringField.

If you don't want to upgrade your PostgreSQL server you can use CAST in your query:
SELECT LOCAL.*,
cast((SELECT N_COMPTE FROM COMPTE WHERE ID_COMPTE = LOCAL.ID_COMPTE) as character varying(6)) as N_COMPTE
FROM LOCAL
For this query TStringField will be created on any PostgreSQL server version.