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@xxxxxxxxx> wrote: > On Tue, May 15, 2012 at 2:28 PM, Mike Christensen <mike@xxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
