I'm keeping track of the unique combinations encountered of three different integer IDs using the solution described in Continuously insert all unique combinations encountered of three IDs. These IDs are a combination of product/group/region IDs etc.
Now I want to bulk update another table by finding the unique IDs that correspond to those combinations. I'm trying to do that using the function update_with_combination_ids
below, but it doesn't work because Postgres has limited support for multi-dimensional arrays. In particular, I'm struggling with implementing WHERE ARRAY[comb.some_id1, comb.some_id2, comb.some_id3] IN (p_combinations[i])
because I can't properly pass an integer[][][]
argument to the Postgres function.
I could implement it as many individual update statements passed as one transaction, but that seems suboptimal from a performance perspective. Ideally, I would love to do this without needing a for-loop, but just having a single function for bulk updating would be a good start.
How do I implement this bulk update?
CREATE TABLE combinations (
id serial PRIMARY KEY,
some_id1 integer NOT NULL,
some_id2 integer NOT NULL,
some_id3 integer NOT NULL
);
CREATE UNIQUE INDEX ON combinations(some_id1, some_id2, some_id3);
CREATE OR REPLACE FUNCTION unnest_2d_1d(ANYARRAY, OUT a ANYARRAY)
RETURNS SETOF ANYARRAY AS
$func$
BEGIN
FOREACH a SLICE 1 IN ARRAY $1 LOOP
RETURN NEXT;
END LOOP;
END
$func$ LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE function create_combinations_if_needed(p_combinations integer[][]) RETURNS boolean
LANGUAGE sql AS
$$
INSERT INTO combinations (some_id1, some_id2, some_id3)
SELECT unnest[1], unnest[2], unnest[3]
FROM unnest_2d_1d(p_combinations) AS unnest
ON CONFLICT (some_id1, some_id2, some_id3)
DO NOTHING
RETURNING TRUE;
$$;
SELECT * FROM create_combinations_if_needed('{{1, 10, 100}, {2, 11, 101}}'::integer[]);
CREATE OR REPLACE FUNCTION update_with_combination_ids(p_row_ids integer[], p_combinations integer[][][]) RETURNS boolean
LANGUAGE plpgsql AS
$$
BEGIN
-- Find the combination IDs in combinations table and and save them into this table
FOR i in 1..array_length(p_row_ids, 1) LOOP
UPDATE user_info
SET combination_ids = matched_combinations.ids
FROM
(
SELECT ARRAY_AGG(mc.id ORDER BY mc.id) AS ids
FROM combinations comb
WHERE ARRAY[comb.some_id1, comb.some_id2, comb.some_id3] IN (p_combinations[i])
) AS matched_combinations
WHERE user_info.id = p_row_ids[i];
END LOOP;
RETURN TRUE;
END;
$$;