pass multiple arrays as input to a function in PostgreSQL

Question

I have a requirement to pass 2 arrays as input to a function

array 1: acct_num, salary etc

array 2: {1011,'Unit 102, 100 Wester highway, Paramataa'} {1012,'+61426999888'}

In above example, array 2 can be dynamic, meaning they can pass upto 500 keys How to process each array key and the value, because I ned to store address information in addresss table and phone number in PHONE table.

I need assistance to access each element in array, but I dont know how to process second elemtn in array 2 (ex:+61426999888)

CREATE OR REPLACE FUNCTION schema.test(
arraytext character varying[],
arraydomain character varying[][])
RETURNS integer AS
$BODY$
DECLARE 

BEGIN   


 p_v1_1    := arraytext[1];

 p_v2_1    := generate_subscripts($1, arraydomain[1]); --arraydomain[1];
 p_v2_2    := arraydomain[2]; 

raise notice 'p_v1_1 : %', p_v1_1;
raise notice 'p_v2_1 : %', p_v2_1;
raise notice 'p_v2_2 : %', p_v2_2;

 p_v2_3    := arraydomain[3];
 p_v2_4    := arraydomain[4]; 


raise notice 'p_v2_3 : %', p_v2_3;
raise notice 'p_v2_4 : %', p_v2_4;


        RETURN 0;
--EXCEPTION WHEN others THEN
  --      RETURN 1;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Then I use:

SELECT * 
FROM schema.test(ARRAY['9361030699999'], ARRAY[['1011','Unit 102, 100 Wester highway, Paramataa'],['1012','+61426999888']]);

Answer 1

Here's a function showing a couple ways of accessing multidimensional arrays. One just loops through the array using a slice, which is the easiest way - the c variable simply exists so I can print the "outer" index, it's not necessary at all.

The other way accesses the values directly. However I don't know how to get each "subarray" itself via index access - e.g. ar[2:2] returns {{values}}, (ar[2:2])[1] returns NULL, and (ar[2:2])[1][1] returns the value of the item in the subarray at that index - the middle one returning NULL is something I don't get. If you could get it, then you could use ARRAY_UPPER to access all the values dynamically without using FOREACH/SLICE.

Also notice I don't declare TEXT[][] - it makes no difference.

CREATE OR REPLACE FUNCTION public.f1(ar TEXT[])
        RETURNS VOID AS
$BODY$
DECLARE
        _ar TEXT[];
        c INTEGER := 0;
BEGIN
        FOREACH _ar SLICE 1 IN ARRAY ar LOOP
                c := c + 1;
                FOR i IN 1..ARRAY_UPPER(_ar, 1) LOOP
                        RAISE NOTICE '%.%: %', c, i, _ar[i];
                END LOOP;
        END LOOP;

        RAISE NOTICE 'Alternative: %, %', (ar[2:2])[1][1], (ar[2:2])[1][2];
END
$BODY$
        LANGUAGE plpgsql IMMUTABLE;

Call:

SELECT * FROM public.f1(ARRAY[['1011','Unit 102, 100 Wester highway, Paramataa'],['1012','+61426999888']]);

Prints:

NOTICE:  1.1: 1011 
NOTICE:  1.2: Unit 102, 100 Wester highway, Paramataa 
NOTICE:  2.1: 1012 
NOTICE:  2.2: +61426999888 
NOTICE:  Alternative: 1012, +61426999888