>>>> 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), ...); That's definitely an approach. I think I know the possible options anyway. I just wanted to make sure there wasn't anything I was missing. Thanks for your help! Mike -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
