Creating a database using a stored function

Question

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

CREATE OR REPLACE FUNCTION mt_test(dbname character varying) RETURNS integer AS $BODY$ Create Database $1; Select 1; $BODY$ LANGUAGE sql; 

When I try 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 pointed out in other answers, this is not just possible, because (for documentation) :

CREATE DATABASE cannot be executed inside a transaction block.

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

What is still missing is the correct function that actually does this:

 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 db exists in the local cluster. If not, proceed with its creation using a disinfected identifier. We would not like to invite SQL injection.

Answer 2

I found a difficult solution to this problem, but possible. After I looked and read almost everywhere, I tried something, and it worked.

if the error "CREATE DATABASE cannot be executed from a function or a line with several commands", we can force the use of one command line using dblink. And do it to connect to yourself.

Check dblink installation instructions on dblink

 PERFORM replication.dblink_connect('myconn','host=127.0.0.1 port=5432 dbname=mydb user=username password=secret'); PERFORM replication.dblink_exec('myconn', 'CREATE DATABASE "DBFROMUSER'||id||'" TEMPLATE "TEMPL'||type||'";',false); PERFORM replication.dblink_disconnect('myconn'); 

In my case, using different types of patterns.

Hello

Answer 3

You cannot create a database inside a function because it is not possible to create a database inside a transaction .

But, most likely, you are not going to create databases, but schemas that are more reminiscent of MySQL databases.

Answer 4

 postgres=> create or replace function mt_test(dbname text) returns void language plpgsql as $$ postgres$> begin postgres$> execute 'create database '||$1; postgres$> end;$$; CREATE FUNCTION postgres=> select work.mt_test('dummy_db'); ERROR: CREATE DATABASE cannot be executed from a function or multi-command string CONTEXT: SQL statement "create database dummy_db" PL/pgSQL function "mt_test" line 2 at EXECUTE statement postgres=> 

note the error message: CREATE DATABASE cannot be executed from a function or multi-command string

so the answer to the question is:

Does postgresql support statement creation in a stored function

- "no" (at least at 8.4 - you will not indicate your version)