>> 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