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)

Answer 1

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;