Interesting.  Thanks!
I've moved a lot of tables to materialized views (mviews), that
basically are flat views of often used tables.  I'm planning on updating
them with triggers. Many of the most referenced tables I have are
actually not updated that often.  Maybe a new record every few days.

It amazes me every time I rework some code in Postgres when I end up
with a fairly simple query to replace gobs of opal code.

On 8/20/2020 11:16 PM, Tom Krieg wrote:
> 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.
>>