On Tue, May 15, 2012 at 3:16 PM, Mike Christensen <mike@xxxxxxxxxxxxx> wrote: >>> I've never done that in PG before, but I've used named connections >>> with Oracle. Is it the same sort of deal? There's a file on the disk >>> somewhere with the connection info? Either way, I'm sure it's a RTFM >>> thing so I'll look into it. >> >> yeah, there's a good example in the docs here: >> http://www.postgresql.org/docs/9.1/static/contrib-dblink-connect.html >> >> btw, if you have a structure in test that matches production, then you >> can use a composite type trick to avoid having to specify fields as >> long as you keep those structures in sync (which you have to do >> anyways). try: >> >> select (u).* from dblink( >> 'hostaddr=123.123.123.123 dbname=ProductionDB user=ROUser >> password=secret', >> 'select u from users u') as t1(u users); >> >> it should work as long as users exists on both sides and has exactly >> the same structure. using that method it's trivial to make a dblink >> wrapper that could query any table but you couldn't wrap it into a >> single view obviously. > > Ah ok, now I'm following.. Yea, I had read up on the dblink_connect() > function, however it seemed like an extra step to have to open this > connection every time. It would avoid duplicating the connection info > across multiple views though. What I was hoping for was the ability > to store this information somewhere. Doesn't PG allow custom > variables for sessions, users, and databases? Or is this something > that could be stored in pg_*.conf or as an environment variable? yes, they are called 'tables' :-). stick your connection string in a table somewhere and do: create view v as select (u).* from dblink((select connstr from yadda where yadda), ...); merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
