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?

Penal

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.

Spleen

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.

Secretary

I don't want to create schemas. I want to create database only. Yes you are right in MyQL I have impletemented it as in MySQL there is no difference between Schema and Database. – Penal

Answer 3

I found a tricky solution to this problem, but possible. After looking and reading almost in everywhere I tried something and it worked.

if the error is "CREATE DATABASE cannot be executed from a function or multi-command string" we can force a single command string using dblink. And make it to connect to itself.

Check for dblink installation instructions at 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 kinds of template.

Greetings

Inutility

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:

Does postgresql support creating statement in stored function

is "no" (at least on 8.4 - you don't specify your version)

Presentment

Is there any way I can implement it? – Penal

Do you mean can you change Postgres? Of course you can if you want to. I am doubtful this is what you mean however. I think it might help if you explain exactly why you want to use multiple databases - what are you trying to achieve? – Presentment

no I am not saying I want to change postgres. I have a database which is currently holding all the data in one table. where as i have multiple users who does not access each others data so I want to store them separately to increase the performance so, I want to create independent database for each user. – Penal

so far I haven't heard any reason not to use schemas rather than databases - have you some other reason? Or better still keep them in one table and give them access to it only through a view that uses session_user. Splitting them up will not 'increase the performance' it will only "increase your workload" – Presentment