How to iterate ARRAY of ARRAY in PostgreSQL to insert values in to another table

38 Views Asked by At

I'm trying to define some csv rows as in the below code, and iterate them so that I can insert those into a new table. But while executing this it complains with the below error:

SQL Error [42804]: ERROR: FOREACH loop variable must not be of an array type.

DO $$
DECLARE
    contact_mapping TEXT[][] := ARRAY[
        ARRAY['1', '1', '1', '1', 'false', 'true', '[email protected]'],
        ARRAY['2', '1', '1', '1', 'true', 'false', '[email protected]'],
        ARRAY['3', '1', '1', NULL, 'false', 'true', '[email protected]']
    ];
    row_data text[];
BEGIN
    FOREACH row_data IN ARRAY contact_mapping
    LOOP
        INSERT INTO contact_notification (
            article_id,
            contact_id,
            is_free,
            is_public
        )
        SELECT
            ca.article_id,
            ec.contact_id,
            (row_data[5] = 'true')::BOOLEAN,
            (row_data[6] = 'true')::BOOLEAN
        FROM article ca
        CROSS JOIN contact ec
        WHERE (ec.email = row_data[7])
        AND (row_data[1] IS NULL OR ca.cl_one = row_data[1])
        AND (row_data[2] IS NULL OR ca.cl_two = row_data[2])
        AND (row_data[3] IS NULL OR ca.cl_three = row_data[3])
        AND (row_data[4] IS NULL OR ca.cl_four = row_data[4]);
    END LOOP;
END$$;
0

There are 0 best solutions below