Thread: Create database/table using postgresql stored function

Hi All,
 
I want to create postgresql database and tables using stored functions.
 
 
Does postgresql support create statements in stored functions.
 
Regards,
Manasi

Also I want to query multiple databases from stored functions from one central database.
 
for ex := 
 

CREATE OR REPLACE FUNCTION test ()  RETURNS  AS
$BODY$
 
SELECT * FROM db2.volume
ORDER BY db2.volume.volumeID
 
$BODY$  
LANGUAGE sql STABLE  
COST 100  
ROWS 1000;'
 
I have implemented this thing in mysql but I am not really sure is it possible in postgresql.

--
Regards,
Manasi Save



On Wed, 08 Dec 2010 04:43:50 -0500, Manasi Save wrote:

Hi All,
 
I want to create postgresql database and tables using stored functions.
 
 
Does postgresql support create statements in stored functions.
 
Regards,
Manasi

Le 08/12/2010 10:43, Manasi Save a écrit :
> [...]
> I want to create postgresql database and tables using stored functions.
>
> Does postgresql support create statements in stored functions.
>

You cannot use CREATE DATABASE from a stored function or in a
multistatements query. But AFAICT, any of the other DDL statements are
available in a stored function (or in a transaction for that matter).


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

Le 08/12/2010 11:04, Manasi Save a écrit :
> Also I want to query multiple databases from stored functions from one
> central database.
> for ex :=
>
> CREATE OR REPLACE FUNCTION test ()  RETURNS  AS
> $BODY$
>
> SELECT * FROM db2.volume
> ORDER BY db2.volume.volumeID
>
> $BODY$
> LANGUAGE sql STABLE
> COST 100
> ROWS 1000;'
>
> I have implemented this thing in mysql but I am not really sure is it
> possible in postgresql.

Nope, you can't use the same kind of queries. You need to install dblink
or PL/proxy to get that kind of capacity.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com