Thread: CREATE SCHEMA $1?

Hi all,

I to create schema with arbitrary name but it seems CREATE SCHEMA doesn't
accept anythig different from a constant as an argument. I've tried with a
function (CREATE SCHEMA some_func();), a SELECT (CREATE SCHEMA (SELECT
somename FROM sometable);) or as a function (CREATE SCHEMA $1; inside a
function definition) but the parser always throw an error at the first
carachter after "SCHEMA".

I can make a little perl script to make the work, but I assume I'm just
missing a bit somewhere...any hint?

I'm using postgres 7.3.2 (on a debian sarge, r1-5).

--
wtf

wtf wrote:
> I to create schema with arbitrary name but it seems CREATE SCHEMA doesn't
> accept anythig different from a constant as an argument. I've tried with a
> function (CREATE SCHEMA some_func();), a SELECT (CREATE SCHEMA (SELECT
> somename FROM sometable);) or as a function (CREATE SCHEMA $1; inside a
> function definition) but the parser always throw an error at the first
> carachter after "SCHEMA".
>
This problem exists because the utility statements currently do not accept
arguements.  This means you have two choices:
1)  Use perl (I think this will work)
2)  Use plpgsql, generate a dynamic query and use the execute statement to
do this.  Something like:
CREATE FUNCTION my_c_schema(NAME)
RETURNS BOOL AS '
    DECLARE
        schema_name ALIAS OF $1;
    BEGIN
        EXECUTE ''CREATE SCHEMA " || schema_name;
    END;
' LANGUAGE PLPGSQL;

Note that the execute string is enclosed in double single-quotes.  Then you
can call this function as:
select my_c_schema(schema_name);
and it will create a schema named schema_name.

Best Wishes,
Chris Travers

P.S.  It would be REALLY nice if the documentation on SQL language functions
contained a note about this problem :)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> I [need to] to create schema with arbitrary name but it seems CREATE SCHEMA
> doesn't accept anythig different from a constant as an argument.

The CREATE SCHEMA command needs to be passed a literal string;
one way around this problem is to use a function:

CREATE OR REPLACE FUNCTION createschema(TEXT) RETURNS VOID AS '
BEGIN
EXECUTE \'CREATE SCHEMA \' || quote_ident($1);
RETURN;
END;
' LANGUAGE plpgsql STABLE RETURNS NULL ON NULL INPUT;


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200311291203
-----BEGIN PGP SIGNATURE-----

iD8DBQE/yNnbvJuQZxSWSsgRAhu1AJ0eqZdsCJuIyZqzpYnJg1rIwetxbQCgwy0h
BqUz0q5cvztSJ4NkuUGysdg=
=UMR0
-----END PGP SIGNATURE-----