1

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

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

1 Answer 1

0

Just set search_path appropriately:

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

Tables that are created without an explicit schema end up in the first existing schema on your search_path.

4
  • Thanks for the reply Laurenz . The stored procedure is actually saved in the public schema. However I am trying to call that same stored procedure from another schema. I ran the code above and this is the error msg I got. ERROR: procedure create_authtable(unknown) does not exist LINE 2: CALL create_authtable('person'); ^ HINT: No procedure matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 30 Nov 5, 2021 at 10:01
  • My omission. Just add the schema name to the procedure call. Nov 5, 2021 at 10:04
  • just ran the code with the omission. SET search_path = rccg; CALL public.create_authtable('person'); I got the error message below: ERROR: invalid transaction termination CONTEXT: PL/pgSQL function public.create_authtable(character varying) line 33 at COMMIT SQL state: 2D000 Nov 5, 2021 at 10:22
  • That's an unrelated problem. Remove the COMMIT from the procedure definition. Nov 5, 2021 at 10:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.