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?
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 DATABASEcannot 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.
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.
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
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)