连续插入遇到的所有三个ID的唯一组合 [英] Continuously insert all unique combinations encountered of three IDs

问题描述

我想用三个不同ID遇到的所有唯一组合来不断更新表。这些ID可以是产品/组/区域ID等。我已经在此处进行了抽象。我想通过调用 create_combinations_if_needed（'{{1，10，100}，{2，11，101}}'）来更新此表，这将创建组合 {1，10，100} 和 {1，10，100} （如果尚不存在）。

I want to continuously update a table with all the unique combinations encountered of three different IDs. These IDs can be product/group/region IDs etc. I've abstracted that away here. I want to update this table by calling create_combinations_if_needed('{{1, 10, 100}, {2, 11, 101}}'), which will create the combinations {1, 10, 100} and {1, 10, 100} if they don't already exist.

这是我下面尝试做的事情。

Here is my attempt at doing that below.

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 create_combinations_if_needed(p_combinations integer[][]) RETURNS boolean
LANGUAGE sql AS
$$
   INSERT INTO combinations (some_id1, some_id2, some_id3)
   SELECT some_id1, some_id2, some_id3
   FROM UNNEST(p_combinations) AS comb(some_id1, some_id3, some_id3)
   ON CONFLICT (some_id1, some_id2, some_id3)
   DO NOTHING
   RETURNING TRUE;
$$;

但是，如果尝试创建此函数，则会出现以下错误：

However, if I try to create this function I get the following error:

ERROR:  table "comb" has 1 columns available but 3 columns specified
CONTEXT:  SQL function "create_combinations_if_needed"

我在做什么错了？

推荐答案

正如@a_horse_with_no_name所述：unnest（）函数不仅展平第一维，而且展平所有嵌套元素。因此，它为每个整数创建一行。那当然会导致一列具有（对于您而言）六个值。这就是异常消息的含义：您生成一个列，但预期会生成三列。

As @a_horse_with_no_name already stated: The unnest() function flattens not only the first dimension but all nested elements. So it creates one row per integer. That, of course results in one column with (in your case) six values. This is what the exception message means: You generate one column but three were expected.

因此，您需要一个仅嵌套第一维的解决方案。我正在使用此处提出的解决方案：

So, you need a solution to unnest only the first dimension. I am using the solutions presented here:

• https://stackoverflow.com/a/8142998/3984221 @LukasEklund和@ErwinBrandstetter

• https://stackoverflow.com/a/8142998/3984221 from @LukasEklund and @ErwinBrandstetter

demo：db<>小提琴

创建Lukas函数：

CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray)
  RETURNS SETOF anyarray AS
$func$
SELECT array_agg($1[d1][d2])
FROM   generate_subscripts($1,1) d1
    ,  generate_subscripts($1,2) d2
GROUP  BY d1
ORDER  BY d1
$func$  LANGUAGE sql IMMUTABLE;

这个仅嵌套第一个维度。因此，您可以使用它代替您的unnest（）在函数中尝试：

This one is unnesting only the first dimension. So you can use it instead of your unnest() try within your function:

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

这篇关于连续插入遇到的所有三个ID的唯一组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！