Postgresql

如何在公共架構中執行儲存過程以在新架構中配置和創建表

  • November 5, 2021

我正在建構一個具有共享數據庫和不同模式的多租戶應用程序（每個客戶在模式中都有其獨立的數據）我已經執行了一個儲存過程，它在公共模式中創建所有表和關係，我如何使用這個相同的儲存過程在新模式中創建相同的表…以租戶名稱作為輸入。查找過程範例

CREATE OR REPLACE PROCEDURE create_member_profile(t_name1 varchar(30), t_name2 varchar(30), t_name3 varchar(30), t_name4 varchar(30), t_name5 varchar(30), t_name6 varchar(30), t_name7 varchar(30), t_name8 varchar(30), t_name9 varchar(30), t_name10 varchar(30))
LANGUAGE plpgsql    
AS $$
BEGIN
EXECUTE format('
       CREATE EXTENSION IF NOT EXISTS "uuid-ossp"');
EXECUTE format('
       DROP TABLE IF EXISTS church_branch, fam, person,
       kid, roles, permissions, person_role, role_permission,
       person_permission, person_note CASCADE');
EXECUTE format('           
       CREATE TABLE %I (   
       "person_id" uuid  NOT NULL,
       "church_branch_id" serial NOT NULL,
       "per_title" varchar(50) NOT NULL,
       "per_first_name" varchar(50) NOT NULL,
       "per_mid_name" varchar(50),
       "per_last_name" varchar(50) NOT NULL,
       "per_address" varchar(500) NOT NULL,
       "per_landmark" varchar(255),
       "per_city" varchar(255) NOT NULL,
       "per_state" varchar(255) NOT NULL,
       "per_country" varchar(255) NOT NULL,
       "per_zip" varchar(255) NOT NULL,
       "per_home_phone" varchar(255),
       "per_work_phone" varchar(255),
       "per_email" varchar(255) NOT NULL,
       "per_username" varchar(50) NOT NULL,
       "per_password" varchar(255) NOT NULL,
       "per_birth_date" DATE NOT NULL,
       "per_join_date" DATE NOT NULL,
       "per_gender" varchar(10) NOT NULL,
       "per_fam_id" uuid NOT NULL,
       "per_entered_by" varchar(255) NOT NULL,
       "per_date_entered" DATE NOT NULL,
       "per_last_edit_by" varchar NOT NULL,
       "per_date_last_edited" DATE NOT NULL,
       "per_flag" varchar NOT NULL,
       "per_facebook" varchar(50) NOT NULL,
       "per_twitter" varchar(50) NOT NULL,
       "per_linkedin" varchar(50) NOT NULL,
       "per_end_date" DATE,
       "per_end_cause" varchar(255),
       "per_membership_status" varchar(255) NOT NULL,
       "per_profession" varchar(255) NOT NULL,
       "per_maiden_name" varchar(255) NOT NULL,
       "per_baptism_date" DATE NOT NULL,
       "per_how_join" varchar(255) NOT NULL,
       "per_about" TEXT NOT NULL,
       "profile_pic_url" varchar(255),
       "per_marital_status" varchar(255),
       "per_jobtitle" varchar(255),
       "per_employer" varchar(255),
       "per_employer_address" varchar(255),
       "per_highest_qualification" varchar(255),
       "per_ethnicity" varchar(255),
       "per_industry" varchar(255) NOT NULL,
       "per_preferred_comm_method" varchar(255) NOT NULL,
       "per_professional_interest" varchar(50) NOT NULL,
       "per_age_group" varchar NOT NULL,
       "created_at" TIMESTAMP,
       "updated_at" TIMESTAMP,
       CONSTRAINT "person_pk" PRIMARY KEY ("person_id")
       ) WITH (
         OIDS=FALSE
       )', t_name1);

EXECUTE format('
       CREATE TABLE %I (
       "fam_id" uuid NOT NULL,
       "fam_name" varchar(50),
       "family_head" uuid NOT NULL,
       "fam_address" varchar(255) NOT NULL,
       "fam_city" varchar(50),
       "fam_state" varchar(50),
       "fam_zip" varchar(50),
       "fam_country" varchar(50),
       "fam_wedding_date" DATE,
       "fam_last_edit_by" varchar NOT NULL,
       "fam_entered_by" varchar NOT NULL,
       "created_at" TIMESTAMP,
       "updated_at" TIMESTAMP,
       CONSTRAINT "family_pk" PRIMARY KEY ("fam_id")
       ) WITH (
         OIDS=FALSE
       )',t_name2);     

EXECUTE format('
       CREATE TABLE %I (
       "branch_id" serial NOT NULL,
       "branch_name" varchar(255),
       "branch_type" varchar(50) NULL,
       "branch_city" varchar(255),
       "branch_state" varchar(255),
       "branch_country" varchar(255),
       "branch_date_created" DATE,
       "branch_head" uuid NOT NULL,
       "branch_status" varchar(255),
       "created_at" TIMESTAMP,
       "updated_at" TIMESTAMP,
       CONSTRAINT "church_branch_pk" PRIMARY KEY ("branch_id")
       ) WITH (
             OIDS=FALSE
       )', t_name3);

EXECUTE format('
       CREATE TABLE %I (
       "kid_id" uuid NOT NULL,
       "kid_hobbies" varchar(1000) NOT NULL,
       "kid_school_class" varchar(255) NOT NULL,
       "created_at" TIMESTAMP NOT NULL,
       "updated_at" TIMESTAMP NOT NULL,
       CONSTRAINT "kid_pk" PRIMARY KEY ("kid_id")
       ) WITH (
         OIDS=FALSE
       )',t_name4);

EXECUTE format('
       CREATE TABLE %I (
       "role_id" serial NOT NULL,
       "role_title" varchar(255) NOT NULL,
       "role_desc" TEXT NOT NULL,
       "created_at" TIMESTAMP NOT NULL,
       "updated_at" TIMESTAMP NOT NULL,
       "role_status" varchar(255) NOT NULL,
       CONSTRAINT "roles_pk" PRIMARY KEY ("role_id")
       ) WITH (
         OIDS=FALSE
       )',t_name5);

EXECUTE format('
       CREATE TABLE %I (
       "perm_id" serial NOT NULL,
       "perm_title" varchar (255) NOT NULL,
       "perm_desc" TEXT NOT NULL,
       "created_at" TIMESTAMP NOT NULL,
       "updated_at" TIMESTAMP NOT NULL,
       "perm_status" varchar(255) NOT NULL,
       CONSTRAINT "permissions_pk" PRIMARY KEY ("perm_id")
       ) WITH (
         OIDS=FALSE
       )',t_name6);

EXECUTE format('
       CREATE TABLE %I (
       "person_id" uuid NOT NULL,
       "role_id" integer NOT NULL,
       "start_date" TIMESTAMP NOT NULL,
       "end_date" TIMESTAMP NOT NULL,
       CONSTRAINT "person_role_pk" PRIMARY KEY ("person_id","role_id")
       ) WITH (
        OIDS=FALSE
       )',t_name7);
   
EXECUTE format('
       CREATE TABLE %I (
       "role_id" serial NOT NULL,
       "perm_id" serial NOT NULL,
       "start_date" TIMESTAMP NOT NULL,
       "end_date" TIMESTAMP NOT NULL,
       "created_at" TIMESTAMP NOT NULL,
       "updated_at" TIMESTAMP NOT NULL,
       CONSTRAINT "role_permission_pk" PRIMARY KEY ("role_id","perm_id")
       ) WITH (
        OIDS=FALSE
       )',t_name8);

EXECUTE format('
       CREATE TABLE %I (
       "person_id" uuid NOT NULL,
       "perm_id" serial NOT NULL,
       "start_date" TIMESTAMP NOT NULL,
       "end_date" TIMESTAMP NOT NULL,
       CONSTRAINT "person_permission_pk" PRIMARY KEY ("person_id","perm_id")
       ) WITH (
        OIDS=FALSE
       )',t_name9);

EXECUTE format('
       CREATE TABLE %I (
       "note_id" serial NOT NULL,
       "person_id" uuid NOT NULL,
       "note" TEXT NOT NULL,
       "note_date_created" TIMESTAMP NOT NULL,
       "note_date_last_updated" TIMESTAMP NOT NULL,
       "note_privacy" varchar(50) NOT NULL,
       CONSTRAINT "person_note_pk" PRIMARY KEY ("note_id")
       ) WITH (
        OIDS=FALSE
       )',t_name10);

EXECUTE format('
       ALTER TABLE %I
       ADD CONSTRAINT "person_fk0" FOREIGN KEY ("church_branch_id") REFERENCES "church_branch"("branch_id")',t_name1);

EXECUTE format('
       ALTER TABLE %I
       ADD CONSTRAINT "person_fk1" FOREIGN KEY ("per_fam_id") REFERENCES "fam"("fam_id")', t_name1);           

EXECUTE format('
       ALTER TABLE %I
       ADD CONSTRAINT "fam_fk0" FOREIGN KEY ("family_head") REFERENCES "person"("person_id")', t_name2);

EXECUTE format('
       ALTER TABLE %I
       ADD CONSTRAINT "church_branch_fk0" FOREIGN KEY ("branch_head") REFERENCES "person"("person_id")', t_name3);

EXECUTE format('
       ALTER TABLE %I
       ADD CONSTRAINT "kid_fk0" FOREIGN KEY ("kid_id") REFERENCES "person"("person_id")', t_name4);

EXECUTE format('
       ALTER TABLE %I
       ADD CONSTRAINT "person_role_fk0" FOREIGN KEY ("person_id") REFERENCES "person"("person_id")', t_name7);

EXECUTE format('
       ALTER TABLE %I
       ADD CONSTRAINT "person_role_fk1" FOREIGN KEY ("role_id") REFERENCES "roles"("role_id")', t_name7);

EXECUTE format('
       ALTER TABLE %I
       ADD CONSTRAINT "role_permission_fk0" FOREIGN KEY ("role_id") REFERENCES "roles"("role_id")',t_name8);
EXECUTE format('        
       ALTER TABLE %I     
       ADD CONSTRAINT "role_permission_fk1" FOREIGN KEY ("perm_id") REFERENCES "permissions"("perm_id")', t_name8);

EXECUTE format('
       ALTER TABLE %I
       ADD CONSTRAINT "person_permission_fk0" FOREIGN KEY ("person_id") REFERENCES "person"("person_id")',t_name9);

EXECUTE format('        
       ALTER TABLE %I
       ADD CONSTRAINT "person_permission_fk1" FOREIGN KEY ("perm_id") REFERENCES "permissions"("perm_id")', t_name9);

EXECUTE format('
       ALTER TABLE %I
       ADD CONSTRAINT "person_note_fk0" FOREIGN KEY ("person_id") REFERENCES "person"("person_id")', t_name10);
        
COMMIT;END;$$

只需search_path適當設置：

SET search_path = schema_for_tables;
CALL public.create_member_profile(...);

在沒有顯式架構的情況下創建的表最終會出現在您的search_path.

引用自：https://dba.stackexchange.com/questions/302100

相關問答

Postgresql

為具有多種使用者類型的場景設計多租戶數據庫

  • May 13, 2021
檢視問答
Postgresql

PostgreSQL：在循環中切換模式名稱

  • March 5, 2021
檢視問答
Postgresql

將數據從公共模式複製到 postgresql 中的另一個模式

  • November 18, 2014
檢視問答
Postgresql

在使用多模式設計支持多租戶時處理使用者登錄

  • September 8, 2014
檢視問答
Postgresql

將 search_path 設置為角色似乎在 postgres 中不起作用

  • September 15, 2022
檢視問答
Postgresql

在實現 PostgreSQL 行級安全性時，如何提供對所有使用者通用的某些行的訪問權限？

  • July 28, 2022
檢視問答