>>>> 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