postgresql Postgress:自定义upsert(存储过程?)一次插入多行时如何?

kmb7vmvb  于 2023-08-04  发布在  PostgreSQL
关注(0)|答案(3)|浏览(122)

有一张table:

create table mytab(
  r char(38) not null,
  h char(2),
  p bytea not null,
  primary key (r,h)
)

字符串
我一次插入数百行,像这样:

insert into mytab (r, h, p) values 
('val_r', 'a0', 'pr_1'), ('r1', 'a1', 'p1')......


我需要将常规的insert替换为“smart”:
1.如果没有这样的键(r,h),则插入行
1.如果键存在且值(p)与new相同,则不执行任何操作
1.如果键存在但值不同-仅对该特定行抛出错误,以某种方式让应用程序知道该特定行有错误-并继续处理其他行
我读过关于insert ... on conflict do update/nothing的文章,但它看起来不适用。由于规则3,在文档中我没有发现类似“do fail”的内容。
那么,我如何才能做到这一点,一次插入许多行?可能是类似于存储过程的东西,但如何实现呢?
PgSQL 15,psycopg2.由于性能损失,一次只使用一行insert不是一种选择。

ruarlubt

ruarlubt1#

您不需要一次执行一行,但仍然可以使用ON CONFLICT。使用两个查询,一个用于插入所有值,另一个用于查找未满足条件的值。您甚至可以使用CTE将它们放入单个语句中:

WITH input(r, h, p) AS (VALUES
  ('val_r'::char(38), 'a0'::char(2), 'pr_1'::bytea),
  ('r1', 'a1', 'oops this is different'),
  ('new', 'kx', 'anything')
),
inserted AS (
  INSERT INTO mytab (r, h, p)
  SELECT * FROM input
  ON CONFLICT DO NOTHING
  RETURNING *
)
SELECT r, h, mytab.p, input.p AS p_attempted
FROM input
JOIN mytab USING (r, h)
WHERE input.p <> mytab.p;

字符串
online demo
如果不需要区分已经存在的行是否具有相同的值,也可以只获取未使用

…
TABLE input
EXCEPT
TABLE inserted;


online demo

2ul0zpep

2ul0zpep2#

为表创建触发器。

create or replace function before_insert_on_mytab()
returns trigger language plpgsql as $$
declare
    rec record;
begin
    select *
    from mytab
    where r = new.r and h = new.h
    into rec;
    if rec is not null then
        if rec.p <> new.p then
            -- notice
            raise notice '%', new;
            -- notify
            perform pg_notify('mytab_log', new::text);
            -- log table
            insert into mytab_errors values
            (new.r, new.h, new.p);
        end if;
        return null;
    end if;
    return new;
end $$;

字符串
trigger函数过滤插入的行,并将不正确的行报告为通知、通道通知或特殊表(您选择)。
db<>fiddle.中测试

7gyucuyw

7gyucuyw3#

可以创建一个带有out参数的过程,并使用cursor从表中选择行并循环遍历每一行。在表mytab中检查是否存在任何像这样的带有r,h的记录。

DECLARE
existing_count bigint DEFAULT 0;
r_value char(38);
h_value char(2);
p_value bytea;
v_query text;
c1 CURSOR for SELECT r,h,p FROM temp_table;
BEGIN
OPEN c1;
FETCH c1 INTO r_value, h_value, p_value;
EXIT WHEN NOT FOUND;  
SELECT COUNT(*) INTO existing_count FROM mytab WHERE r = r_value AND h = h_value AND p <> p_value;
IF existing_count > 0 THEN
  CONCAT(COALESCE(p_out, ''), E'\n Failed for r = ', r_value, ' , h = ', h_value, ' and p = ', p_value);
END IF;
v_query := CONCAT('INSERT INTO mytab (r,h,p) VALUES (',r_value,',',h_value,',', p_value,') ON CONFLICT (r,h) DO NOTHING;');
EXECUTE v_query;
CLOSE c1;
END;

字符串
这只是游标代码而不是程序。这里我使用了p_out,它将成为存储过程的out参数。首先将所有记录插入temp_table。在调用该过程时,out参数将提供输出,其中包含每行上失败行的详细信息。

相关问题