>> 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. > > yeah, there's a good example in the docs here: > http://www.postgresql.org/docs/9.1/static/contrib-dblink-connect.html > > btw, if you have a structure in test that matches production, then you > can use a composite type trick to avoid having to specify fields as > long as you keep those structures in sync (which you have to do > anyways). try: > > select (u).* from dblink( > 'hostaddr=123.123.123.123 dbname=ProductionDB user=ROUser > password=secret', > 'select u from users u') as t1(u users); > > it should work as long as users exists on both sides and has exactly > the same structure. using that method it's trivial to make a dblink > wrapper that could query any table but you couldn't wrap it into a > single view obviously. Ah ok, now I'm following.. Yea, I had read up on the dblink_connect() function, however it seemed like an extra step to have to open this connection every time. It would avoid duplicating the connection info across multiple views though. What I was hoping for was the ability to store this information somewhere. Doesn't PG allow custom variables for sessions, users, and databases? Or is this something that could be stored in pg_*.conf or as an environment variable? Eventually, this whole thing might evolve into a Python script or something that can migrate data over, and at that point the script could open the connection and migrate the appropriate data over. Also, thanks for the tip using the "users" type rather than explicitly spelling out each column. The majority of my tables are identical in schema, so this would work great. However, sometimes I add new columns in the development schema, and I have to account for that difference on the INSERT line, such as: INSERT INTO Recipes SELECT *, false as SomeNewColumn FROM ProdLink.Recipes WHERE ... So, this may or may not work. Still, good to know! Mike -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
