How to put argument (table name) of function?

Question

I am using PostgreSQL 14.4 . My script

-- 0.
DROP TABLE IF EXISTS tenant;
CREATE TABLE tenant
(
    id               smallint primary key,
    company_tax_code character varying(14),
    period           character varying(16), -- 2021070420220705
    created          timestamp with time zone
);

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

BEGIN
    new.id = (select coalesce(max(id), -32769) from tenant) + 1;
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER trigger_insert_without_id_tenant
    BEFORE INSERT
    ON tenant
    FOR EACH ROW
EXECUTE PROCEDURE set_id_tenant();

CREATE INDEX tenant_idx ON tenant (id);
COMMIT;
--------------------------------------------------------------------------------
-- 4.
DROP TABLE IF EXISTS account_object_bank_account;
CREATE TABLE account_object_bank_account
(
    id                smallint,
    account_object_id smallint not null,
    bank_account      character varying(64),
    bank_name         character varying(128),
    bank_id           smallint,
    sort_order        smallint,
    bank_branch_name  character varying(256),
    province          character varying(128),
    tenant_id         smallint,
    PRIMARY KEY (id, tenant_id),
    CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES tenant (id)
);

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

BEGIN
    new.id = (select coalesce(max(id), -32769) from account_object_bank_account where tenant_id = new.tenant_id) + 1;
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER account_object_bank_account_trig_insertWithoutId
    BEFORE INSERT
    ON account_object_bank_account
    FOR EACH ROW
EXECUTE PROCEDURE account_object_bank_account_setId();

CREATE INDEX account_object_bank_account_idx ON account_object_bank_account (id, tenant_id);

COMMENT ON TABLE public.account_object_bank_account IS 'Bảng lưu tài khoản ngân hàng của khách hàng';
COMMENT ON COLUMN public.account_object_bank_account.id IS 'PK';
COMMENT ON COLUMN public.account_object_bank_account.account_object_id IS 'FK';
COMMENT ON COLUMN public.account_object_bank_account.bank_account IS 'Số TK ngân hàng';
COMMENT ON COLUMN public.account_object_bank_account.bank_name IS 'Tên ngân hàng';
COMMIT;
--------------------------------------------------------------------------------
-- 5.
DROP TABLE IF EXISTS account_object_belong_to_group;
CREATE TABLE account_object_belong_to_group
(
    id                      smallint,
    account_object_id       smallint,
    account_object_group_id smallint,
    tenant_id               smallint,
    PRIMARY KEY (id, tenant_id),
    CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES tenant (id)
);

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

BEGIN
    new.id = (select coalesce(max(id), -32769) from account_object_belong_to_group where tenant_id = new.tenant_id) + 1;
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER account_object_belong_to_group_trig_insertWithoutId
    BEFORE INSERT
    ON account_object_belong_to_group
    FOR EACH ROW
EXECUTE PROCEDURE account_object_belong_to_group_setId();

CREATE INDEX account_object_belong_to_group_idx ON account_object_belong_to_group (id, tenant_id);
COMMIT;
--------------------------------------------------------------------------------

I want re-use this custom function, then put argument of function is table name:

account_object_bank_account_setId()
account_object_belong_to_group_setId()

to

set_id( table_name )

argument sample: account_object_bank_account, account_object_belong_to_group.

How to do?

Answer 1

update your 2 triggers with set_id('table_name')

and use TG_ARGV[] to retrieve the parameter in the trigger function

https://www.postgresql.org/docs/14/plpgsql-trigger.html