只有在记录存在时才插入Postgres表

Question

我正在用Postgres编写一些sql来更新审计表。我的sql将根据某些标准更新正在审核的表，然后选择更新的记录以更新审核表中的信息。到目前为止，这就是我所拥有的：

DO $$
  DECLARE
    jsonValue json;
    revId int;
    template RECORD;
    BEGIN
        jsonValue = '...Some JSON...'
        UPDATE projectTemplate set json = jsonValue where type='InstallationProject' AND account_id IS NULL;

        template := (SELECT pt FROM ProjectTemplate pt WHERE pt.type='InstallationProject' AND pt.account_id IS NULL);

        IF EXISTS (template) THEN
        (
            revId := nextval('hibernate_sequence');
            insert into revisionentity (id, timestamp) values(revId, extract(epoch from CURRENT_TIMESTAMP));

            insert into projectTemplate_aud (rev, revtype, id, name, type, category, validfrom, json, account_id)
                VALUES (revId, 1, template.id, template.name, template.type, template.category, template.validfrom, jsonValue, template.account_id);
        )
END $$;

我的理解是，如果表中没有匹配该查询的任何内容(而当前没有)，那么template将是未定义的。我希望这样做，这样如果template不存在，我的查询就不会尝试更新审核表。

我能做些什么来更新这个sql以匹配我想要做的事情？

Answer 1

不能这样使用EXISTS，它需要一个子查询表达式。再加上代码中的其他一些问题。

这个带有数据修改CTEs的SQL语句应该正确地替换您的DO命令。而且速度也更快：

WITH upd AS (
   UPDATE ProjectTemplate
   SET    json = '...Some JSON...'
   WHERE  type = 'InstallationProject'
   AND    account_id IS NULL
   RETURNING *
   )
, ins AS (
   INSERT INTO revisionentity (id, timestamp)
   SELECT nextval('hibernate_sequence'), extract(epoch FROM CURRENT_TIMESTAMP)
   WHERE  EXISTS (SELECT FROM upd)  -- minimal valid EXISTS expression!
   RETURNING id
   )
INSERT INTO ProjectTemplate_aud
      (rev , revtype,   id,   name,   type,   category,   validfrom,   json,   account_id)
SELECT i.id, 1      , u.id, u.name, u.type, u.category, u.validfrom, u.json, u.account_id
FROM   upd u, ins i;

如果revisionentity找到任何行，则在UPDATE中插入一行。

插入的行projectTemplate_aud数量与行已更新的行数相同。

关于数据修改CTE：

• Insert data in 3 tables at a time using Postgres

旁白:我看到了骆驼的混合，一些下划线，或只是低的名字。只考虑合法的小写名称(并避免将基本类型名称作为列名)。但最重要的是，要始终如一。相关信息：

• Are PostgreSQL column names case-sensitive?
• Misnamed field in subquery leads to join