时间戳

首页 > 解决方案 > 如何避免对默认列值进行多次更新？

postgresql - 如何避免对默认列值进行多次更新？

问题描述

我想在带有可选参数的存储函数中插入记录并返回插入的记录。

表是：

CREATE TABLE partner (
    id serial NOT NULL,
    name text NOT NULL,
    status text not null default 'internal',
    active bool not NULL DEFAULT true
);

请注意，status 和 active 列具有默认值。

Store 函数同时具有必需参数和可选参数。

create or replace function addPartner(pname text, pstatus text = null, pactive bool = null) returns partner as $$
declare
  newrecord partner;
begin
  insert into partner
    (name)
    values(pname)
    returning * into newrecord;
  if pactive is not null then
    update partner set active = pactive where id = newrecord.id returning * into newrecord;
  end if;
  if pstatus is not null then
    update partner set status = pstatus where id = newrecord.id returning * into newrecord;
  end if;
  return newrecord;
end;
$$ language plpgsql;

调用函数如下：

select * from addPartner('customer A', 'external', false);
select * from addPartner('customer B', 'external');
select * from addPartner('customer C');

select * from partner;

如果使用所有参数（“客户 A”）调用该函数，我需要执行插入一条记录，其中包含必需的参数和可选参数的默认值，然后，对于每个可选参数（或具有默认值的列） ) 对可选参数值执行更新。总体而言，1 次插入和 2 次更新。

如果可能，我想避免在存储函数中进行这些多次更新，并在一条 SQL 语句中插入一条新记录。

如果省略了可选参数，则带有参数值或默认值的插入语句。

在 rextester 上与之前的代码链接： https ://rextester.com/POZM9812

标签： postgresql

解决方案

使用动态 SQL：https ://rextester.com/UGA76790

create or replace function addPartner(pname text, pstatus text = null, pactive bool = null) returns partner as $$
declare
  newrecord partner;
  statement TEXT;
begin
  statement := FORMAT(
      'INSERT INTO partner (name, status, active) VALUES ($1, %s, %s) RETURNING *',
      COALESCE(QUOTE_LITERAL(pstatus), 'DEFAULT'),
      COALESCE(QUOTE_LITERAL(pactive), 'DEFAULT')
  );

  EXECUTE statement
  INTO newrecord
  USING pname;

  return newrecord;
end;
$$ language plpgsql;

推荐阅读