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$$;