Re: Is there a way to avoid hard coding database connection info into views?

Thanks!

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.

Mike

On Tue, May 15, 2012 at 12:45 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Tue, May 15, 2012 at 2:28 PM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
>> I often manually pull in production data into my test database so I
>> can test new code on realistic data, as well as test upgrade scenarios
>> or repro data specific bugs.  To do this, I've setup a `VIEW` for each
>> production table in my test database.  These views look something like
>> this:
>>
>>    CREATE VIEW ProdLink.Users AS
>>       select * from dblink(
>>          'hostaddr=123.123.123.123 dbname=ProductionDB user=ROUser
>> password=secret',
>>          'select * from users') as t1(userid uuid, email varchar(50),
>> alias varchar(50), fullname varchar(50), password varchar(100));
>>
>> Now, on my production database I can run:
>>
>>    SELECT * FROM ProdLink.Users;
>>
>> And see all users on my production database.  I can then do things like:
>>
>>    INSERT INTO Users SELECT * FROM ProdLink.Users L WHERE NOT EXISTS
>> (select 1 from Users where Users.UserId = L.UserId);
>>
>> Allowing me to pull in every user from production that doesn't already
>> exist in test.
>>
>> I have about 30 of these views to *proxy* the production data, however
>> I find it somewhat hacky to have to hardcode in the production
>> database connection info into each view.
>>
>> Is there a good way to avoid hardcoding, or at least duplicating this
>> connection info on each view?  Can I use database level variables,
>> environment variables, or anything else instead?
>
> sure: why don't you set up a named connection and just make sure it's
> established before hitting any of the views?
>
> merlin