Re: How to do a "CREATE DATABASE" and then connect to it?

On Sat, 3 Jun 2006 04:36:59 -0400 Terry Lee Tucker <terry(at)esc1(dot)com> thought long, then sat down and wrote:

> On Saturday 03 June 2006 04:07 am, Joseph Brenner <doom(at)kzsu(dot)stanford(dot)edu>
> thus communicated:
> -->
> --> Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> -->
> --> > Joseph Brenner wrote:
> -->
> --> > > After you do a "CREATE DATABASE", how do you programatically
> --> > > connect to what you just created?
> --> > >
> --> > > In the psql monitor, you'd use the "\c" command.
> --> > >
> --> > > If the DATABASE already exists when you connect to postgresql,
> --> > > you use the name when you connect (e.g. "dbname=...").
> --> > >
> --> > > I'm getting the impression I need to do this in multiple steps,
> --> > > which is workable, but seems a little silly.
> -->
> --> > Have you tried reading the documentation?
> -->
> --> Yup. Are you sure you've understood my question?
> -->
> --> It's not a terribly major point, I'm just wondering if it's true that
> --> there's no postgres SQL analog of the psql "\c" command.
> -->
> --> For example, this certainly works in perl:
> -->
> --> use DBI;
> -->
> --> my $dbh_1 = DBI->connect("dbi:Pg:dbname=template1", $owner,
> $db_password, --> { RaiseError => 1, AutoCommit =>
> 1 }); -->
> --> $dbh_1->do("CREATE DATABASE new_test_db");
> -->
> --> $dbh_1->disconnect();
> -->
> --> my $dbh_2 = DBI->connect("dbi:Pg:dbname=new_test_db", $owner,
> $db_password, --> { RaiseError => 1, AutoCommit =>
> 1 }); -->
> -->
> --> $dbh_2->do("CREATE TABLE whocares (meaningless INTEGER, blather
> TEXT)"); -->
> -->
> --> But the need for those two "DBI->connect"s seems inelegant to me.
> -->
> --> Do you have any particular portion of the documentation in mind?
> -->
>
> I don't see the point. There are two connects either way.
> psql:
> Connection 1: psql template1
> Connection 2: \c new_test_db
>
> Perl:
> Connection 1: my $dbh_1 = DBI->connect("dbi:Pg:dbname=template1", blah, blah
> Connection 2: DBI->connect("dbi:Pg:dbname=new_test_db", $owner, blah, blah
>
> The only difference, at least from my point of view, is the method used and
> the extra disconnect(). But even so, the extra disconnect() is not really
> necessary.

I think the point is, that one has not to leave psql to get the new connection, but simply use \c, while with perl / any programming language you have to open a new coennection with a long and complicated command.

But "\c" is _not_ an SQL-command, its simply a shortcut for closing an old connection and creating a new one. You could do something similar in your program creating a function as shortcut to avoid lots of typing everytime you change connection. This function would need $dbh and the new database as parameter, close the old connection and return the new one. That__s equivalent to what "\c" does.

It would sometimes really be nice, btw, if inter-database queries were possible.
--
Frank Finner

Invenius - Lösungen mit Linux
Köpfchenstraße 36
57072 Siegen
Telefon: 0271 231 8606 Mail: frank(dot)finner(at)invenius(dot)de
Telefax: 0271 231 8608 Web: http://www.invenius.de
Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651