From: "Nicolas Mitchell" <mitchelln@posteo.net>
To: pgsql-novice@lists.postgresql.org
Subject: Trigger function
Date: Mon, 26 Jul 2021 15:17:12 +0000
Message-ID: <30B8A3D0-8A87-40E2-BE40-31A17BA4B944@posteo.net>
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/30B8A3D0-8A87-40E2-BE40-31A17BA4B944%40posteo.net>
Precedence: bulk

Hi,

I have a set of tables, ‘object’, ‘obtype’, ‘host’ and 
domain:

CREATE TABLE "domain"
(
"id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1000001 INCREMENT 
BY 1),
"name" VARCHAR(64) NOT NULL,
CONSTRAINT "domain__id__pk" PRIMARY KEY ("id")
);

CREATE TABLE "obtype"
(
"id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 2800001 INCREMENT 
BY 1),
"name" VARCHAR(16) NOT NULL,
CONSTRAINT "obtype__id__pk" PRIMARY KEY ("id")
);

CREATE TABLE "host"
(
"id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 2900001 INCREMENT 
BY 1),
"name" VARCHAR(32) NOT NULL,
"domain" INTEGER NOT NULL,
"object" INTEGER NOT NULL,
CONSTRAINT "host__id__pk" PRIMARY KEY ("id")
);

CREATE TABLE "object"
(
"id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 3200001 INCREMENT 
BY 1),
"type" INTEGER NOT NULL,
CONSTRAINT "object__id__pk" PRIMARY KEY ("id")
);

ALTER TABLE "domain" ADD CONSTRAINT "domain__name__uk" UNIQUE ("name");

ALTER TABLE "obtype" ADD CONSTRAINT "obtype__name__uk" UNIQUE ("name");

ALTER TABLE "host" ADD CONSTRAINT "host__name_domain__uk" UNIQUE 
("name","domain");

ALTER TABLE "host" ADD CONSTRAINT "host__object__uk" UNIQUE ("object");

ALTER TABLE "host" ADD CONSTRAINT "host__object__fk" FOREIGN KEY 
("object") REFERENCES "object" ("id") ON DELETE CASCADE;

ALTER TABLE "host" ADD CONSTRAINT "host__domain__fk" FOREIGN KEY 
("domain") REFERENCES "domain" ("id") ON DELETE RESTRICT;

ALTER TABLE "object" ADD CONSTRAINT "object__type__fk" FOREIGN KEY 
("type") REFERENCES "obtype" ("id") ON DELETE RESTRICT;

I am looking at whether functions can help me automate creating a new 
object when a new host is added.

I can do this manually with the following code:

WITH object_id AS
(INSERT INTO public.object (type)
VALUES (
	( SELECT obtype.id
       FROM public.obtype
       WHERE obtype.name LIKE 'host'
       )
)
RETURNING id)
INSERT INTO host (name, domain, object)
VALUES ('gary', 1000001, (SELECT * FROM object_id));

I have a number of questions but I would like to begin by asking whether 
this a candidate for a trigger function on table ‘host’, triggered 
before an insert?

Many thanks,

NicM