Re: CREATE SCHEMA $1?

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 :)