Re: !! Newbie question!!!! connecting to multiple databases

On Fri, Aug 24, 2001 at 09:53:00PM +0000, uncleleo wrote:
> I am attemping to create multiple databases with Postgresql ver. 7.0.3
> running on Mandrake 8.0 rpm.  The tool that I am using is Pgadmin ver 7.1.0.
>
> Can someone tell me how I can connect to different databases in a single
> select statement?  Such as, I have a database named "Customer" and another
> named "Products".  I wish to Select from table A in the Customer database
> and table A in Products database. I know that its possible in SQL Server and
> other databases.
>
> If anyone can I help I would appreciate it.

postgresql doesn't allow you to connect to anything but tables
in the 'current' database via sql.

but in an external language you can have several connections
open, each to a different database:

    #!perl
    use DBI;
    my $db1 = DBI->connect('dbi:Pg:dbname=people');
    my $db2 = DBI->connect('dbi:Pg:dbname=inventory');

    my $st1 = $db1->prepare('select * from client');
    $st1->execute();

    while ( my $rec = $st1->fetchrow_hashref ) {
        my $st2 = $db2->prepare("select $rec->{afield} from $rec->{atable}");
        $st2->execute();

        foreach my $item ( $st2->fetchrow_hashref ) {
            ...
        }
    }

but it may be a sign that you need to revisit your data
paradigm, instead... (there are some cases where three levels of
structure are handy: db->table->field -- but usually two does
quite nicely: table->field within db.)

--
Hey, let's change the whole justice system. Everybody gets to
kill one person -- if you do two, you go to jail. That should
cut down on the abrasive personalities, don't you think?

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!