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

Question

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

Laurenz Albe · Accepted Answer · 2021-11-05 10:04:01Z

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.

edited Nov 5, 2021 at 10:04

answered Nov 4, 2021 at 7:03

Laurenz Albe

56.5k4 gold badges50 silver badges82 bronze badges

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
– Suleiman Bashir Shamsudeen
Commented Nov 5, 2021 at 10:01
My omission. Just add the schema name to the procedure call.
– Laurenz Albe
Commented 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
– Suleiman Bashir Shamsudeen
Commented Nov 5, 2021 at 10:22
That's an unrelated problem. Remove the COMMIT from the procedure definition.
– Laurenz Albe
Commented Nov 5, 2021 at 10:28

Add a comment |

Stack Exchange Network

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

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
postgresql
stored-procedures
schema
multi-tenant
or ask your own question.

Hot Network Questions

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

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
postgresql
stored-procedures
schema
multi-tenant
or ask your own question.