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

On Tue, May 15, 2012 at 2:28 PM, Mike Christensen <mike@kitchenpc.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