Create Schema functionality question
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
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
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:
Show quoted text
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 likeDECLARE 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
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