Declaring a varray of custom records and using Extend method(which gives a syntax error??)

Question

So, I am working on a View that calls a function. Before I declare the function or View, I am defining a custom composite record. Then, I declare a vararray of type:my custom composite record. Within the function, I then define a new variable of type: vararray mentioned earlier. (I will show example below for clarification). However, when I try to call newCustomArray.extends(1), I get a syntax error. I did not get an error when I was just testing the function by itself (without a view). So I am a little confused, also a little new to plsql for edb postgres. Anyone have any ideas on what I am doing wrong? Here is the code below:

CREATE TYPE public.Question AS
(
 "ID" integer,
 "QID" integer,
 "Order" integer,
 "IsNeeded" boolean,
 "FieldName" character varying(150),
 "OtherField" boolean
);

CREATE TYPE public.infoarray IS VARYING ARRAY(40) OF public.Question;

......

(within function)
rec_anotherinfoarray public.infoarray := public.infoarray();

rec_anotherinfoarray.extend(1) --> causing all the syntax errors (SQL state 42601)

Your `CREATE TYPE...` is an Oracle definition as is the statement `rec_anotherinfoarray.extend(1)' . Postgres **does not have/support varray**, neither does it have extend clause for an arrray. — Belayer, Aug 21 '23 at 19:00
@Belayer but edb postgres does have support though, it is in the documentation here: https://www.enterprisedb.com/docs/epas/latest/reference/application_programmer_reference/stored_procedural_language_reference/11_collection_methods/04_extend/ — JPho, Aug 22 '23 at 02:21
I stand corrected. Good to know. Never worked with later EDB versions and missed the tag. But that becomes an important piece might be best the specifically indicate that along with version. — Belayer, Aug 22 '23 at 02:54

score 0 · Answer 1 · answered Aug 28 '23 at 11:48

VARRAY type is not supported in PostgreSQL's PL/pgSQL language.

In PostgreSQL, VARRAYs are fixed-size arrays, and you cannot dynamically extend or resize them using the EXTEND method like you can with collections in some other database systems.

If you want a collection that you can dynamically extend, you might consider using a PL/pgSQL array instead. Here's an example of how you could modify your code:

CREATE OR REPLACE FUNCTION your_function()
RETURNS void AS $$
DECLARE
    rec_anotherinfoarray public.infoarray[]; -- Use an array instead of VARRAY
BEGIN
    -- Initialize the array
    rec_anotherinfoarray := ARRAY[]::public.infoarray[];

    -- Extend the array
    rec_anotherinfoarray := array_append(rec_anotherinfoarray, public.infoarray(1, 2, 3, true, 'FieldName', true));

    -- You can continue to extend the array as needed

END;
$$ LANGUAGE plpgsql;

Declaring a varray of custom records and using Extend method(which gives a syntax error??)

1 Answers1