Return to Question

added 7618 characters in body

Source Link

edited Nov 4, 2021 at 5:47

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

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;COMMIT;END;$$

END;$$

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

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

Source Link

asked Nov 4, 2021 at 5:37

Suleiman Bashir Shamsudeen

How can I Run stored procedures in public schema to provision and create tables in a new schema

I'm building a multi tenant application with shared database and different schemas(each customer has its isolated data in a schema) I have run a stored procedure that creates all tables and relationships in the public schema, how can I use this same stored procedure to create same tables but in a new schema...taking the tenant name as input.find the sample of a procedure

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