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?
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