23

I have the following problem, I need to put in a script that is going to run before the new version is rolled the SQL code that enables the pgAgent in PostgreSQL. However, this code should be run on the maintenance database (postgres) and the database where we run the script file is another one.

I remember that in SQL Server there is a command "use " so you could do something like:

use foo

-- some code

use bar 

-- more code

is there something similar in PostgreSQL?

Improve this question
1

4 Answers 4

Reset to default
17

You can put in your file something like:

\c first_db_name
select * from t; --- your sql
\c second_db_name
select * from t; --- your sql
...
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

Are you piping these commands through the psql command? If so, \c databasename is what you want.

psql documentation

Improve this answer

5 Comments

Actually the input is a text file that is processed by the database
How it's "processed by the database"? You have to use some Postgres client to connect to it. Which one?
It's a client that executes the text file and it connects to the database through the ODBC driver. To make it easier imagine I'm on the pgAdmin client and I want to run a script that will make an update in two different databases.
JDBC dont recognize \c so I have to connect to specific jdbc URL that I want to connect. And execute SQL file.
If you're using JDBC, then you are not using psql. \c is not SQL - it's a psql command. Postgres database connections can't be switched to a different database on the fly.
3

You can't switch databases in Postgres in this way. You actually have to reconnect to the other database.

Improve this answer

2 Comments

and is there a way to do this from the script or do I have to do it from the application that is connecting to the database?
From the application. If you are done with the first db connection, just drop it, otherwise you'll need to make two connections. I had asked the pg developers the same question a while back. I don't recall their reasons for doing so. But they said that even though pgadmin and psql appear to be just switching databases, they are really establishing a new connection.
1

PostgreSQL doesn't have the USE command. You would most likely use psql with the --dbname option to accomplish this, --dbname takes the database name as a parameter. See this link for details on the other options you can pass in you will also want to check out the --file option as well. http://www.postgresql.org/docs/9.0/interactive/app-psql.html

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.