时间戳

首页 > 解决方案 > 最小化相同子查询的使用

sql - 最小化相同子查询的使用

问题描述

我有以下简化的功能：

CREATE OR REPLACE FUNCTION upsert(varchar, varchar, varchar) RETURNS void AS $$
BEGIN

with t1 as (
    select id from table_a where $1 = a_field
), t2 as (
    select id from table_a where $2 = a_field
)
insert into table_b (tba_ref, status)
values
    ((select id from t1), $3),
    ((select id from t2), $3)
on conflict on constraint uniq_constraint do
update set status =
    (CASE
    WHEN table_b.tba_ref = (select id from t1) THEN 'old'
    WHEN table_b.tba_ref = (select id from t2) THEN 'new'
    END)
;
END;
$$ language 'plpgsql';

有什么办法可以摆脱选择子查询？这只是一个简化的示例，大致反映了原始查询的结构。原来有更多的这两个子查询，我想通过存储一次结果而不是重复运行这些查询来提高性能。

标签： sqlpostgresql

解决方案

我建议将此作为两个单独的更新：

with t1 as (
      select id from table_a where $1 = a_field
    )
insert into table_b (tba_ref, status)
    values ((select id from t1), $3)),
on conflict on constraint uniq_constraint do
    update set status = 'old';

with t2 as (
      select id from table_a where $2 = a_field
    )
insert into table_b (tba_ref, status)
    values ((select id from t2), $3)),
on conflict on constraint uniq_constraint do
    update set status = 'new';

您可以将这些包装在一个事务中。

我认为没有办法将“另一个”列传递给on conflict子句。您insert无法区分这两个值——也就是说'old'，'new'没有被插入到表中。

推荐阅读