Postgres: How to create trigger based on value of insert?-CodePudding

I develop a multi-tenant application in a single database. 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.

I hope you understand my needs and my idea. I need you revise it, make it work.

CodePudding user response：

One way would be to use before insert trigger:

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();

DBFiddle demo here

Note: I am not sure that you need something like this. If I were in your shoes, then I would have an id for the table itself as a primary key with data type serial. Then when needed, I would retrieve the data from that table using 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;

CodePudding user response：

I would suggest instead that this is better done by a many-to-many join table between account and tenant.

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)
);

Insert the account, get its ID, insert that into 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

If the tenant/account relationship needs its own ID, add it to the tenant_accounts table. For example...

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)
);

Then use select max(tenant_account_id) 1 from tenant_accounts where tenant_id = $1 to populate it.

Or if it's just for ordering, add a timestamp and order by that.

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)
);

Some notes.

Probably don't use smallints for IDs, saving 2 or even 6 bytes is not worth the headache of running out of IDs.

Don't put arbitrary constraints on text columns. You're not saving any space. Unless there are specific inherent limits on the size, not arbitrary business rules, use text and let the application apply business rules.

ref_type and voucher_type should probably be foreign keys to a ref and voucher table. That will offer referential integrity.