How to use a trigger in PostgreSQL to create a new schema when inserting a new row into a table?

Question

I have a table containing clients, I have only one client for each country, and for each row in this table I have to create a new schema and copy some tables. Like this:

Clients table columns: client country ---------- ----------- john doe US jane doe UK 

Schemas:

 clients_US clients_UK 

I need to create a trigger to automatically create these schemas every time a new client is added to the client table, using the data from the country column as part of the schema name.

In an ideal world, this will work, but that clearly does not mean:

 CREATE OR REPLACE FUNCTION gcpmanager.create_sle_schema() RETURNS trigger LANGUAGE plpgsql AS 'begin CREATE SCHEMA "clients_" + new.country AUTHORIZATION postgres; return new; end;'; 

Answer 1

You will need dynamic SQL

 CREATE OR REPLACE FUNCTION gcpmanager.create_sle_schema() RETURNS trigger LANGUAGE plpgsql AS $$ begin execute 'CREATE SCHEMA clients_'||new.country||' AUTHORIZATION postgres'; return new; end; $$ 

Btw: string concatenation operator in PostgreSQL || not + (i.e. for numbers)

Answer 2

Use dynamic SQL for this:

http://www.postgresql.org/docs/9.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN