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