From: | Mike Christensen <mike(at)kitchenpc(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Is there a way to avoid hard coding database connection info into views? |
Date: | 2012-05-15 19:28:39 |
Message-ID: | CABs1bs3ZEcw2jj2_D_FPP0mHcJLMvESmBXg0X4Lm0RCLEfaSBg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
PS - I also posted this question on StackOverflow for anyone who wants
points: http://stackoverflow.com/questions/10605622/postgres-9-x-is-there-a-way-to-avoid-hard-coding-database-connection-info-into
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2012-05-15 19:45:18 | Re: Is there a way to avoid hard coding database connection info into views? |
Previous Message | Steve Crawford | 2012-05-15 15:29:35 | Re: Vacuum does not show in pg_stat_all_tables |