[GENERAL] Concat bigint Array Values in a Function

[Vinay Gupta]

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