Create a schema with the name passed by variable

Question

I want to create a schema with with a name passed by variable. Example:

CREATE OR REPLACE FUNCTION test1("name" character varying)
  RETURNS void AS
'CREATE SCHEMA "name";'
  LANGUAGE 'sql' VOLATILE
  COST 100;

Answer 1

You could use plpgsql and than EXECUTE:

CREATE OR REPLACE FUNCTION test1("name" character varying)
RETURNS void AS
$$
BEGIN
    EXECUTE 'CREATE SCHEMA '|| quote_ident($1); -- security

    RETURN;
END;
$$
LANGUAGE plpgsql
VOLATILE
COST 20;

Answer 2

user search_path to change the default schema so you may easily add tables to it! and use format with %I to escape the schema name as identifier.

like this:

CREATE OR REPLACE FUNCTION test1("name" character varying)
RETURNS void AS
$$
BEGIN
    EXECUTE FORMAT('CREATE SCHEMA %I;', $1);
    EXECUTE FORMAT('SET search_path TO %I;', $1);

    CREATE TABLE table1(
    column1 integer
    );

    RETURN;
END;
$$
LANGUAGE plpgsql
VOLATILE
COST 20;