Re: Connect to a database in a .sql file

On Tue, 2005-11-08 at 07:31, Assad Jarrahian wrote:
> Hi,
>     Lets say the script is called myDBSetup.sql
>
> And the script contains:
>
> //CREATE DATABASE section
> //CREATE USERS SECTION
> //COnnect to db
> //CREATE TABLES, FUNCTIONS etc.
>
> this script will be called from psql. The user will log connect to
> template1 and then run my script. What I really need is after the
> CREATE DB, I need to switch from template1 to the database name (so
> the CREATE tables etc will correspond to the right db). This has to be
> automated and done within the script.
>
> \c does not work in .sql script run in psql.
>
> Any suggestions would be helpful. Thanks.
> -assad
>

As others have mentioned \c works from sql script, small example follows

-- test.sql
CREATE database test3;
\c test3
CREATE TABLE test_table (
  field1 integer,
  field2 varchar(10));
INSERT INTO test_table (field1, field2) VALUES (1,'VALUE1');
INSERT INTO test_table (field1, field2) VALUES (2,'VALUE2');
SELECT * FROM test_table;
-- Cut

[post800b1@raftahlid35 projects]$ psql -a -f test.sql template1
CREATE database test3;
CREATE DATABASE
\c test3
You are now connected to database "test3".
CREATE TABLE test_table (
  field1 integer,
  field2 varchar(10));
CREATE TABLE
INSERT INTO test_table (field1, field2) VALUES (1,'VALUE1');
INSERT 25513 1
INSERT INTO test_table (field1, field2) VALUES (2,'VALUE2');
INSERT 25514 1
SELECT * FROM test_table;
 field1 | field2
--------+--------
      1 | VALUE1
      2 | VALUE2
(2 rows)

--
Sigurdur