Paradox Community

Items in pnews.paradox-client_server

Subject:Re: Postgresql text columns in memory TC
Date:Fri, 21 Aug 2020 14:16:41 +1000
From:Tom Krieg <>
 From Postgres - "varchar(n) and text use the same backend storage type 
(varlena): a variable length byte array with a 32bit length counter. For 
indexing behavior text may even have some performance benefits. It is 
considered a best practice in Postgres to use text type for new 
development; varchar(n) remains for SQL standard support reasons. NB: 
varchar() (with empty brackets) is a Postgres-specific alias for text."

Maximum length for a text field is approx 1GB for single byte 
characters. Paradox has a limit of 256MB for memo/graphics/binary fields.

When I've extracted variable data to a Paradox table, I've always done 
it with functions and declared a return type of "Set of values" and 
defined a composite type with the structure exactly as I want the output 
table to be, with data types compatible with Paradox. Here's an example:
(The resulting Paradox table is the exact same structure as the 
composite type except varchar has been converted to Alpha. Any database 
text fields get truncated to the varchar defined length.)

It would be possible (and not too difficult) to extract a text field to 
a table of records in chunks, and then reassemble it (in Paradox) into a 
memo field.

CREATE TYPE public.typappliancelist AS (
   appliance_uid INTEGER,
   model_uid INTEGER,
   model_number VARCHAR(155),
   model_description VARCHAR(155),
   model_variant VARCHAR(155),
   generic_description VARCHAR(155),
   serial_number VARCHAR(75),
   manufacture_date VARCHAR(25),
   retailer_uid INTEGER,
   retailer_name VARCHAR(155),
   owner_uid INTEGER,
   owner_name VARCHAR(155),
   phone_primary VARCHAR(25),
   purchase_date DATE

CREATE OR REPLACE FUNCTION public."GetApplianceList" (
   brandproduct char,
   usergroupuuid char,
   model char
RETURNS SETOF public.typappliancelist AS
   apps typappliancelist%rowtype;
   modeluid integer;
   brandproductuid integer;
   SELECT CAST(brandproduct AS INTEGER) INTO brandproductuid;
   SELECT CAST(model AS INTEGER) INTO modeluid;
   FOR apps IN
       SELECT A.appliance_uid
             ,CASE WHEN A.model_variant IS NULL THEN M.model_variance
                   ELSE A.model_variant
             ,CASE WHEN  A.retailer_uid Is NULL THEN NULL
                   ELSE  A.retailer_uid
             ,CASE WHEN (A.retailer_uid Is NULL Or A.retailer_uid = 0)
                   THEN A.retailer_name
                   ELSE (SELECT R.retailer_name || ' '
                             || R.retailer_location
                         FROM public.retailers R
                         WHERE R.retailer_uid = A.retailer_uid
                         AND   R.usergroup_uuid = A.usergroup_uuid)
             ,CASE WHEN C.contact_type = 'B'
                   THEN C.business_name
                   ELSE RTRIM(LTRIM(C.first_name || ' '
                                 || C.last_name))
       FROM public.appliances A
                   LEFT JOIN public.models M
                        ON A.model_uid = M.brandmodel_uid
                       AND A.usergroup_uuid = M.usergroup_uuid
                   LEFT JOIN public.contacts C
                        ON A.owner_uid = C.contact_uid
                       AND A.usergroup_uuid = C.usergroup_uuid
       WHERE A.brandproduct_uid = brandproductuid
       AND   A.model_uid = modeluid
       AND   A.usergroup_uuid = usergroupuuid
       RETURN NEXT apps;
LANGUAGE 'plpgsql'
COST 100 ROWS 1000;

On 21/08/2020 11:44 am, Tom Krieg wrote:
> I think you'll need to change the data type of the output field. 
> Whenever I extract data I do it through a function, using a record type 
> as the return type. With Paradox I redefined text as varchars in the 
> output and I didn't have any problems.

Copyright © 2004