By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,662 Members | 1,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,662 IT Pros & Developers. It's quick & easy.

Create Schema functionality question

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
Seamus Thomas Carroll <ca******@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #2

P: n/a
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 <ca******@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #3

P: n/a
Seamus Thomas Carroll <ca******@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.