PostgreSQL Automatically create Functions when a database is created

Question

I have a requirement where I have to create a new database in PostgreSQL server for every user that signs in. Hence 1 database per user. Also, that database will have few custom defined functions that will be called. I want to automate this process of creating the custom defined functions as soon as I create a database. So that I don't have to explicitly go and execute the function in each database.

Is this possible?

Is it possible? Yes, create the functions in the template1 database. Does it make sense? Most probably not. — user330315
– user330315, Commented Apr 18, 2017 at 19:30
as @a_horse_with_no_name said, you can create all needed relations in sample database and then "clone" it with create database new_db with template sample_db; — Vao Tsun
– Vao Tsun, Commented Apr 18, 2017 at 19:33
Although a template database is a good solution, I would prefer something like Liqubase or Flyway to manage the whole database setup script (including tables and functions). That script should be stored in a version control system like git or Subversion to manage those scripts (to prevent something like this) — user330315
– user330315, Commented Apr 18, 2017 at 19:58

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

From docs:

CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1. Thus that database is the "template" from which new databases are made. If you add objects to template1, these objects will be copied into subsequently created user databases. This behavior allows site-local modifications to the standard set of objects in databases. For example, if you install the procedural language PL/Perl in template1, it will automatically be available in user databases without any extra action being taken when those databases are created.

So you can create "default" objects in template1 db and "clone" it. Or you can create all needed relations (tables, functions, etc) in sample database and then "clone" it with

create database new_db with template sample_db;

Same way you can create several template databases with different "default" pre-created objects and just "clone" them as needed.

Read docs above about limitations etc.

Update Adding Postgres Objects to Template1 will show you how to do it with pgadmin

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 18, 2017 at 19:48

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

You don't need a special sample database for that. It's perfectly fine to create those functions in template1

Vao Tsun Over a year ago

yes, but I thought, next OP's question would be how to regenerate template1 database to "default", so I thought coming this step and showing how to create a database with template.

puneet Over a year ago

How do I create a function inside the template1 database using pgAdmin client? I do not see any way I can load the template1 database, however, I do see the postgres database . Is there a statement, which i can load the context of template1 db, and whatever functions i create, get created under the template1 db then. Like in SQL we had the 'using <database name>' command

Collectives™ on Stack Overflow

PostgreSQL Automatically create Functions when a database is created

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related