Postgres Pro
Downloads
Home   >   mailing lists

Thread: Create Schema functionality question

Create Schema functionality question

From
Seamus Thomas Carroll
Date:
Hi,

I am trying to create a schema where the schema name has been defined in
another table.  My sql looks like:
create schema (select agent_schema from agents.agents where agent_id =
(select(last_value) from agents.agents_seq));

The select statement works correctly on its own but when added to
"create schema" I get the following error:
ERROR:  syntax error at or near "(" at character 15

Is there any possible way to create a schema without having
to hard code the name?

Seamus

Re: Create Schema functionality question

From
Tom Lane
Date:
Seamus Thomas Carroll <carrolls@cpsc.ucalgary.ca> writes:
> Is there any possible way to create a schema without having
> to hard code the name?

Not in CREATE SCHEMA itself.  But you could use a plpgsql or pltcl
function to construct the needed command as a string, and execute
that.  In plpgsql it'd look something like

    DECLARE newschema text;
    ...
    SELECT INTO newschema agent_schema FROM ... WHERE ...;
    EXECUTE "CREATE SCHEMA " || quote_ident(newschema);
    ...

The same goes for other utility commands (which is to say everything
except SELECT/INSERT/UPDATE/DELETE).

            regards, tom lane

Re: Create Schema functionality question

From
Seamus Thomas Carroll
Date:
From what I have just read EXECUTE requires a prepared statement but
prepared statements only work with SELECT, INSERT, UPDATE, or DELETE.

Does your suggestion take this into account? If so can you explain
further?  Is your suggestions meant for "psql"?

Seamus

On Tue, 24 Feb 2004, Tom Lane wrote:

> Seamus Thomas Carroll <carrolls@cpsc.ucalgary.ca> writes:
> > Is there any possible way to create a schema without having
> > to hard code the name?
>
> Not in CREATE SCHEMA itself.  But you could use a plpgsql or pltcl
> function to construct the needed command as a string, and execute
> that.  In plpgsql it'd look something like
>
>     DECLARE newschema text;
>     ...
>     SELECT INTO newschema agent_schema FROM ... WHERE ...;
>     EXECUTE "CREATE SCHEMA " || quote_ident(newschema);
>     ...
>
> The same goes for other utility commands (which is to say everything
> except SELECT/INSERT/UPDATE/DELETE).
>
>             regards, tom lane
>

Re: Create Schema functionality question

From
Tom Lane
Date:
Seamus Thomas Carroll <carrolls@cpsc.ucalgary.ca> writes:
> From what I have just read EXECUTE requires a prepared statement but
> prepared statements only work with SELECT, INSERT, UPDATE, or DELETE.

You're confusing SQL EXECUTE with plpgsql's EXECUTE.  They're not
related.  The coincidence of names is unfortunate but we're stuck
with it ...

            regards, tom lane