Concat bigint Array Values in a Function

Hi,

I need to optimise and aggregate array integer values in a function and i
am doing this by below stored function :

-- type def
CREATE TYPE fun_type AS (
g_id integer,
zip_id_list integer[],
city_id_list integer[],
state_id_list integer[],
lat_long_id_list bigint[],
country_id_list bigint[],
ccid_list bigint[],
cr_id_list bigint[],
is_active boolean
);

-- fun def

CREATE OR REPLACE FUNCTION fun_multicountry()
RETURNS SETOF fun_type AS
$BODY$
DECLARE
row1 fun_type%ROWTYPE;
dt1 record;
dt1outer record;
BEGIN
FOR dt1outer IN SELECT DISTINCT g_id FROM tableA
LOOP
row1.country_id_list = '{}';
row1.state_id_list = '{}';
row1.city_id_list = '{}';
row1.lat_long_id_list = '{}';
row1.zip_id_list = '{}';
row1.ccid_list = '{}';
row1.cr_id_list = '{}';
row1.g_id = dt1outer.g_id;
row1.is_active = false;
FOR dt1 IN SELECT * FROM tableA LEFT OUTER JOIN tableB
ON (pr_id = tableB.id) where
g_id = *dt1outer.g_id* AND tableA.is_active = true

LOOP
row1.is_active = true;
IF(dt1.geot_type_id =1 and dt1.pr_id is not NULL)
THEN
row1.cr_id_list = row1.cr_id_list|| dt1.targeting_ids;
ELSIF( (dt1.state_id_list is null or dt1.state_id_list =
'{}') AND
(dt1.city_id_list is null or dt1.city_id_list = '{}') AND
(dt1.lat_long_id_list is null or dt1.lat_long_id_list =
'{}') AND
(dt1.zip_id_list is null or dt1.zip_id_list = '{}'))
THEN
row1.country_id_list = row1.country_id_list ||
dt1.country_id;
ELSE
row1.state_id_list = row1.state_id_list ||
dt1.state_id_list;
row1.city_id_list = row1.city_id_list || dt1.city_id_list;
row1.lat_long_id_list = row1.lat_long_id_list ||
dt1.lat_long_id_list;
row1.zip_id_list = row1.zip_id_list || dt1.zip_id_list;
END IF;
IF(dt1.ccid_list is null or dt1.ccid_list = '{}')
THEN
row1.ccid_list = row1.ccid_list || -dt1.country_id;
ELSE
row1.ccid_list = row1.ccid_list || dt1.ccid_list;
END IF;
END LOOP;
return NEXT row1;
END LOOP;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;

Above function is taking ~ 30 -40 sec to finish where as table rows are :

explain analyze select fun_multicountry();

QUERY PLAN

----------------------------------------------------------------------------------------------------

Result (cost=0.00..5.25 rows=1000 width=0) (actual
time=21855.881..21959.683 rows=420286 loops=1)

Total runtime: 31977.712 ms

(2 rows)

Time: 21978.103 ms

select count(*) from tableA;

count

--------

629439

(1 row)

Time: 135.858 ms

select count(*) from tableB;

count

-------

841

select count(distinct g_id) from tableA;

count

--------

420287

I need to aggregate all different array values in table depending upon
above if conditions and return them. Is dere any optimal approach to do
this as i am calling this function in many other functions and its just
adding to slowness. I am trying to rewrite using CASE statements. Will this
help ?

Please let me know if there is any optimal way to finish this function in ~
2-3 secs

Thanks