Loop through a composite array in plpgsql

user_7020915 0
Viewed 1012

I want to define a composite array , initialize three elements of the array and then iterate over each of the elements in a loop. I need this loop and to iterate over it as a requirement for an issue that I have.

I have tried for several days to write the code and refer to several resources on net but this has not worked. Can someone help on it. Here is the code, even the first initialization is not working so I don't have code for rest of the requirement (to iterate after the initialization)

CREATE TYPE temp_n_inv_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);



CREATE OR REPLACE function temp_n_bulk_load2()
returns void
as
$$
declare
   v_t temp_n_inv_item[]; 


BEGIN


      v_t[1] := ARRAY ['Item1',1,10];
      v_t[2] := ARRAY ['Item2',2,20];
      v_t[3] := ARRAY ['Item3',3,30];


 --  raise notice 'first array % ', v_t[1];
--raise notice 'first array % ', v_t[2];
--raise notice 'first array % ', v_t[3];

--iterate though each of the v_t array in a loop 

null; --added as a temporary placeholder 

END;
$$
language plpgsql;

--select temp_n_bulk_load2()

Regards, DbuserN

UPDATE Surprisingly, after posting the question, I saw one reference and assignment is now working but I try to change the return type to the array which is not working, (which is an additional issue), though assignment is fixed now as below, but the error the below gives is "ERROR: cannot return non-composite value from function returning composite type" Revised code :

CREATE OR REPLACE function temp_n_bulk_load2()
returns temp_n_inv_item
as
$$
declare
   v_t temp_n_inv_item[]; 


BEGIN


      v_t[1] := row ('Item1',1,10);
      v_t[2] := row ('Item2',2,20);
      v_t[3] := row ('Item3',3,30);


  raise notice 'first array % ', v_t[1];
raise notice 'first array % ', v_t[2];
raise notice 'first array % ', v_t[3];

--iterate though each of the v_t array in a loop 

null; --added as a temporary placeholder 
return v_t;

END;
$$
language plpgsql;
plpgsql postgresql
2 Answers

PLpgSQL has special statement for iteration over array. For large arrays it is much more effective (is not too significant for small arrays):

create type tp as (a int, b int);

do $$
declare a tp[];
          r record;
begin
  /* composite array initialization */
  a = array[(1,2),(3,4),(5,6)];

  /* iterate over a array */
  foreach r in array a
  loop
    raise notice '% % %', r, r.a, r.b;
  end loop;
end;
$$;

NOTICE:  (1,2) 1 2
NOTICE:  (3,4) 3 4
NOTICE:  (5,6) 5 6
DO

Don't afraid read a documentation.

user_4066910

Here you are creating array of data type you generated using 

CREATE TYPE temp_n_inv_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

So simply return array of data type as temp_n_inv_item[].

CREATE OR REPLACE FUNCTION adm.temp_n_bulk_load2()
  RETURNS boolean AS
$BODY$
declare
   v_t temp_n_inv_item[]; 


BEGIN

/*CREATE TYPE temp_n_inv_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);*/
      v_t[1] := row ('Item1',1,10);
      v_t[2] := row ('Item2',2,20);
      v_t[3] := row ('Item3',3,30);


raise notice 'first array % ', v_t[1];
raise notice 'first array % ', v_t[2];
raise notice 'first array % ', v_t[3];

--iterate though each of the v_t array in a loop 
//updated
  FOR cnt in 1..(array_length(v_t,1))
            LOOP
Raise notice ' array value % ', v_t[cnt];

 END LOOP;

return true;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Hope this will solve your problem.

user_99127060
Related