I am relatively new to SQL function and I am trying to create a trigger that inserts into another table some values from the current table I am inserting into. I have a 2d array which I am not sure how to iterate over as it keeps giving an error: ERROR: FOREACH loop variable must not be of an array type
In basic terms, I am trying to zip 2 array columns from the new entry (which we can assume will never be null but may be empty), check the other table I want to insert to and delete any of the rows that don't match the new values, then insert the new values in the table.
I ideally it should only zip the 2 array columns up the minimum length of the 2 arrays so if we have ["t"] and ["e", "d"] we should only have [["t", "e"]] in the new zipped 2d array. I haven't implemented this portion since I'm not sure how to do it.
Here is what I have tried for the basic insertion/update of the other table that gives me the error:
CREATE OR REPLACE FUNCTION INSERT_OR_UPDATE_TABLE_B() RETURNS trigger AS
$$
DECLARE zipped_new varchar[][];
DECLARE new_entry varchar[];
DECLARE temprow RECORD;
BEGIN
SELECT INTO zipped_new ARRAY[field1, field2]
FROM unnest(new.fields1 , new.fields2) x(field1,field2);
--zipped_new = array_agg((new.fields1, new.fields2));
FOR temprow in SELECT * FROM table_b WHERE id = old.id
LOOP
IF ARRAY[temprow.field1, temprow.field2] != ALL (zipped_new)
THEN
-- field1 and field2 are the primary keys of table_b
DELETE FROM table_b WHERE temprow.field1 = field1 AND temprow.field2 = field2;
END IF;
END LOOP;
IF zipped_new IS NOT NULL
THEN
FOREACH new_entry IN ARRAY zipped_new
LOOP
-- new_entry[1] should insert in field1 and new_entry[2] should insert in field2
INSERT INTO table_b VALUES (new.id, new_entry[1], new_entry[2]) ON CONFLICT DO NOTHING;
end loop;
end if;
RETURN NULL;
END
$$
LANGUAGE PLPGSQL;
DROP TRIGGER IF EXISTS INSERT_OR_UPDATE_TABLE_B ON table_a;
CREATE TRIGGER INSERT_OR_UPDATE_TABLE_B
BEFORE INSERT OR UPDATE
ON table_a
FOR EACH ROW
EXECUTE PROCEDURE INSERT_OR_UPDATE_TABLE_B();