Create database using a stored function

Question

I am new to PostgreSQL and want to create a database using a stored function.
For ex:

CREATE OR REPLACE FUNCTION mt_test(dbname character varying)
  RETURNS integer AS
$BODY$

Create Database $1;

Select 1;

$BODY$
  LANGUAGE sql;

When I am trying to execute this function I get a syntax error.

Does Postgres support the CREATE DATABASE statement in stored functions?

Answer 1

This question is old, but for the sake of completeness ...

As has been pointed out in other answers, that's not simply possible because (per documentation):

CREATE DATABASE cannot be executed inside a transaction block.

It has also been reported that the restriction can be bypassed with dblink.
How to use (install) dblink in PostgreSQL?

What was missing so far is a proper function actually doing it:

CREATE OR REPLACE FUNCTION f_create_db(dbname text)
  RETURNS integer AS
$func$
BEGIN

IF EXISTS (SELECT 1 FROM pg_database WHERE datname = dbname) THEN
   RAISE NOTICE 'Database already exists'; 
ELSE
   PERFORM dblink_exec('dbname=' || current_database()   -- current db
                     , 'CREATE DATABASE ' || quote_ident(dbname));
END IF;

END
$func$ LANGUAGE plpgsql;

Checks if the db already exists in the local cluster. If not, proceed to create it - with a sanitized identifier. We would not want to invite SQL injection.

Answer 2

You can't create a database inside of a function because it's not possible to create a database inside a transaction.

But most probably you don't mean to create databases but schemas, which more closely resemble the MySQL's databases.