Bulk updating PostgreSQL table using lookup in ano

2019-08-31 09:12发布

问题:

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