Postgres：如何根据插入值创建触发器？

Question

我在一个数据库中开发了多租户应用程序。 My table

DROP TABLE IF EXISTS account_default;
CREATE TABLE account_default
(
    id               smallint,
    ref_type         smallint               not null,
    ref_type_name    character varying(256),
    voucher_type     smallint               not null,
    column_name      character varying(64)  not null,
    column_caption   character varying(128) not null,
    filter_condition character varying(1024),
    default_value    character varying(32),
    sort_order       smallint,
    created          timestamp with time zone,
    created_by       character varying(64),
    modified         timestamp with time zone,
    modified_by      character varying(64),
    tenant_id        smallint,
    PRIMARY KEY (id, tenant_id),
    CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES tenant (id)
);
CREATE INDEX account_default_idx ON account_default (id, tenant_id);

I need composite keys like these

(id, tenant_id)
(1, 1)
(2, 1)
(3, 1)

(1, 2)
(2, 2)
(3, 2)
(4, 2)

(1, 3)
(2, 3)

I tried something like this (base on insert value of tenant_id).

select (coalesce(max(id), 0) + 1) as next_val from account_default where tenant_id = 1;

CREATE TRIGGER account_default_id_trigger AFTER UPDATE ON account_default
FOR EACH ROW 
...

When run insert script

INSERT INTO public.account_default(
    ref_type, ref_type_name, voucher_type, column_name, column_caption, filter_condition, default_value, sort_order, created, created_by, modified, modified_by, tenant_id)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 5);

INSERT INTO public.account_default(
    ref_type, ref_type_name, voucher_type, column_name, column_caption, filter_condition, default_value, sort_order, created, created_by, modified, modified_by, tenant_id)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 5);

INSERT INTO public.account_default(
    ref_type, ref_type_name, voucher_type, column_name, column_caption, filter_condition, default_value, sort_order, created, created_by, modified, modified_by, tenant_id)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 5);

for example, tenant_id = 5, id auto insert based on trigger.

希望您了解我的需求和我的想法。我需要您修改它，使它起作用。

Answer 1

一种方法是在插入触发器之前使用：

CREATE OR REPLACE FUNCTION setId()
RETURNS trigger AS
$
     DECLARE

     BEGIN
          new.id = (select count(*) from account_default where tenant_id = new.tenant_id)+1;
          RETURN NEW;
     END;
$ LANGUAGE 'plpgsql';

CREATE TRIGGER trig_insertWithoutId
     BEFORE INSERT ON account_default
     FOR EACH ROW EXECUTE PROCEDURE setId();

”不确定您需要一些东西 像这样。如果我穿着您的鞋子，那么我将拥有桌子本身的ID作为数据类型串行的主要键。然后，在需要时，我将使用row_number（）从该表中检索数据。 IE：

select row_number() over (partition by tenant_id order by created) as myId, tenant_id
from account_default
order by tenant_id;

Answer 2

我建议取而代之的是，这是通过帐户和租户之间的多对多加入表来完成的。

create table account (
  -- Running out of IDs is the worst.
  id bigserial primary key,
 
   -- and all the rest
);

create table tenant_accounts (
  account_id bigint not null references(account),
  tenant_id bigint not null references(tenant),

  -- Assuming a tenant cannot be linked to the same account
  -- twice.
  unique(account_id, tenant_id)
);

插入帐户，获取ID，将其插入tenant_accounts。

with insert_account as (
  insert into account ... returning id;
)
insert into tenant_accounts(account_id, tenant_id)
select insert_account.id, $1
from insert_account

---

如果房客/帐户关系需要自己的ID，请将其添加到the the the the the the the the the the the the the the the the the the table表中。例如...

create table tenant_accounts (
  account_id bigint not null references(account),
  tenant_id bigint not null references(tenant),
  tenant_account_id not null smallint,

  unique(account_id, tenant_id),
  unique(tenant_account_id, tenant_id)
);

然后使用从tenant_accounts中选择最大（tenant_account_id） + 1，其中tenant_id = $ 1将其填充。

或者，如果只是订购，请添加时间戳并订购。

create table tenant_accounts (
  account_id bigint not null references(account),
  tenant_id bigint not null references(tenant),
  created_at timestamp not null default current_timestamp,

  unique(account_id, tenant_id)
);

---

一些笔记。

可能不使用小键来用于ID，保存2个或什至6个字节不值得耗尽ID的头痛。

不要在文本列上任意约束。您没有节省任何空间。除非对大小有特定的固有限制，而不是任意业务规则，否则请使用文本，并让应用程序应用业务规则。

ref_type和voucher_type可能应该是ref> ref和代金券表的外国密钥。这将提供参考完整性。