How to iterate a 2d array in a PostgreSQL function?

Viewed 51

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();

0 Answers
Related