Subject: | Re: Postgresql text columns in memory TC
| Date: | Fri, 21 Aug 2020 14:16:41 +1000
| From: | Tom Krieg <REMOVEtomkCAPITALS@sdassociates.com.au>
| Newsgroups: | pnews.paradox-client_server
|
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
$body$
DECLARE
apps typappliancelist%rowtype;
modeluid integer;
brandproductuid integer;
BEGIN
SELECT CAST(brandproduct AS INTEGER) INTO brandproductuid;
SELECT CAST(model AS INTEGER) INTO modeluid;
FOR apps IN
SELECT A.appliance_uid
,A.model_uid
,M.model_number
,M.model_description
,CASE WHEN A.model_variant IS NULL THEN M.model_variance
ELSE A.model_variant
END
,A.generic_description
,A.serial_number
,A.manufacture_date
,CASE WHEN A.retailer_uid Is NULL THEN NULL
ELSE A.retailer_uid
END
,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)
END
,A.owner_uid
,CASE WHEN C.contact_type = 'B'
THEN C.business_name
ELSE RTRIM(LTRIM(C.first_name || ' '
|| C.last_name))
END
,C.phone_primary
,A.purchase_date
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
LOOP
RETURN NEXT apps;
END LOOP;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY DEFINER
PARALLEL UNSAFE
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.
>
|