Postgres自动在表中创建记录，然后插入另一个表

Question

Postgres完全陌生。需要能够自动地将几行插入到一个"行"中；文件夹"；表中创建记录时；Companies"；桌子

其理念是，当创建一家公司时，会自动创建一组标准的文档文件夹。其中几行是"子文件夹"，因此需要父文件夹的id。

插入的SQL语句是(注意父文件夹ID需要(：

// Administration
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Administration', NULL, {CompanyID}, NULL, '2022-03-19 15:50:49.579553', '2022-03-19 15:50:49.579553', '');
// Get Insert ID
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Agreements', NULL, {CompanyID}, '{AdministrationFolderID}', '2022-03-19 15:53:24.412457', '2022-03-19 15:53:24.412457', '');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Licensing', NULL, {CompanyID}, '{AdministrationFolderID}', '2022-03-19 15:53:31.780207', '2022-03-19 15:53:31.780207', '');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'QBR & Meeting Notes', NULL, {CompanyID}, '{AdministrationFolderID}', '2022-03-19 15:53:31.780207', '2022-03-19 15:53:31.780207', '');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Reports', NULL, {CompanyID}, '{AdministrationFolderID}', '2022-03-19 15:53:31.780207', '2022-03-19 15:53:31.780207', '');
// Knowlege Base
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Knowledge Base', NULL, {CompanyID}, NULL, '2022-03-19 15:51:53.006775', '2022-03-19 15:51:53.006775', '');
// Get Insert ID
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Configurations', NULL, {CompanyID}, '{KnowledgeBaseFolderID}', '2022-03-19 15:52:54.092854', '2022-03-19 15:52:54.092854', '');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'How-To', NULL, {CompanyID}, '{KnowledgeBaseFolderID}', '2022-03-19 15:52:54.092854', '2022-03-19 15:52:54.092854', '');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Diagrams & Photos', NULL, {CompanyID}, '{KnowledgeBaseFolderID}', '2022-03-19 15:53:05.517208', '2022-03-19 15:53:05.517208', '');

// Other Folders
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'SOPs', NULL, {CompanyID}, NULL, '2022-03-19 15:52:05.152894', '2022-03-19 15:52:05.152894', '');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Projects', NULL, {CompanyID}, NULL, '2022-03-19 15:52:15.818519', '2022-03-19 15:52:15.818519', '');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Strategy', NULL, {CompanyID}, NULL, '2022-03-19 15:52:28.388031', '2022-03-19 15:52:28.388031', '');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, '>Inbox', NULL, {CompanyID}, NULL, '2022-03-19 15:50:26.174575', '2022-03-19 15:51:03.30431', '');

有什么建议吗？

Answer 1

对于任何想要实现这一目标的人：

关键是处理"子"文件夹插入的RETURNING INTO。

CREATE OR REPLACE FUNCTION "public"."function_create_folders"()
RETURNS "pg_catalog"."trigger" AS $BODY$
DECLARE 
admin_id integer;
kb_id integer;
BEGIN 
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Administration', NULL, new.id, NULL, '2022-03-19 15:50:49.579553', '2022-03-19 15:50:49.579553', 'Articles and documents supporting our handling/management of this company.') RETURNING id INTO admin_id;
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Agreements', NULL, new.id, admin_id, '2022-03-19 15:53:24.412457', '2022-03-19 15:53:24.412457', 'Agreements we have with this company');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Licensing', NULL, new.id, admin_id, '2022-03-19 15:53:31.780207', '2022-03-19 15:53:31.780207', 'Licenses and supporting documentation for this company');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'QBR & Meeting Notes', NULL, new.id, admin_id, '2022-03-19 15:53:31.780207', '2022-03-19 15:53:31.780207', 'QBR and Meeting notes.');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Reports', NULL, new.id, admin_id, '2022-03-19 15:53:31.780207', '2022-03-19 15:53:31.780207', 'Reporting respository.');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Knowledge Base', NULL, new.id, NULL, '2022-03-19 15:51:53.006775', '2022-03-19 15:51:53.006775', 'Company specific details, and guides.')  RETURNING id INTO kb_id;   
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Configurations', NULL, new.id, kb_id, '2022-03-19 15:52:54.092854', '2022-03-19 15:52:54.092854', 'Notes and Details, about Assets and Networks for this Company.');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'How-To', NULL, new.id, kb_id, '2022-03-19 15:52:54.092854', '2022-03-19 15:52:54.092854', 'How-To and Guides for solving customer specific issues.');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Diagrams & Photos', NULL, new.id, kb_id, '2022-03-19 15:53:05.517208', '2022-03-19 15:53:05.517208', 'Diagrams and Pictures related to this Company');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'SOPs', NULL, new.id, NULL, '2022-03-19 15:52:05.152894', '2022-03-19 15:52:05.152894', 'Specific SOPs related to this company. (i.e.. New User Onboarding Process, Change Control Process)');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Projects', NULL, new.id, NULL, '2022-03-19 15:52:15.818519', '2022-03-19 15:52:15.818519', 'Notes and details about projects for this company.  ** Create Separate Sub-Folders Per Project **');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, 'Strategy', NULL, new.id, NULL, '2022-03-19 15:52:28.388031', '2022-03-19 15:52:28.388031', 'Guides and Articles about both the customers internal strategy, as well as our strategy for working with them.');
INSERT INTO "public"."folders"("account_id", "name", "icon", "company_id", "ancestry", "created_at", "updated_at", "description") VALUES (1, '>Inbox', NULL, new.id, NULL, '2022-03-19 15:50:26.174575', '2022-03-19 15:51:03.30431', 'In development articles should be saved here.');
RETURN new;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100

CREATE TRIGGER trig_create_folders
AFTER INSERT ON companies
FOR EACH ROW
EXECUTE PROCEDURE function_create_folders();