Hello guys, I’m a new in this forum and I’d like to excuse me for my poor English. There is a difficult case for me to solve so I’d like to ask you for advice. The situation is as follow:
We have a database with many schemas and a role for each schema. Every user works with own role and schema. All the schema contains same objects – tables, indexes, constraints, views, functions etc. There is also one “default” schema called “template_schema” which is empty and is used for template for creating of new schemas. I have to automate the creation of new schemas and when a new user is registered new empty schema has to be created for him. I have a function which is executed on user registration and creates new empty schema and role for this schema, but cannot fill this new schema with objects from template schema. I have to do this with postgresql functions because I don’t have access to shell – pg_dump, psql.. Is there any suggestion how to do that? Thanks in advance. I hope I was clear and excuse me for my English.

First off, sounds like a perfect use for template databases (not schemas!). You can easily create a new database as a copy of an existing template database with a single command.

You can copy a table structure easily (see sql - Copy a table (including indexes) in postgres - Stack Overflow). But to do that for a list of tables you will need to either hardcode the table names or put it in a pgsql loop.