Geek asks and answers

Postgres 9.x - Is there a way to avoid hard-coding information about connecting a database to views?

I often manually extract production data to my test database so that I can test the new code for realistic data, as well as test update scripts or replicate specific errors. For this, I set up VIEWfor 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, in my production database, I can run:

SELECT * FROM ProdLink.Users;

And see all the users in my production database. Then I can do things like:

INSERT INTO Users SELECT * FROM ProdLink.Users L WHERE NOT EXISTS (select 1 from Users where Users.UserId = L.UserId);

Allows me to pull each user out of production that does not yet exist in the test.

30 , , , .

: , , ? , - ?

+3
1

, :

CREATE VIEW ProdLink.Users AS
    select * from dblink(
        (select conn_string from conn_string where conn = 'that_one'),
        'select * from users'
    ) as t1 (
        userid uuid, 
        email varchar(50), 
        alias varchar(50), 
        fullname varchar(50), 
        password varchar(100)
    );
+1

More articles:


All Articles