Search Postgresql Archives

Re: Is there a way to avoid hard coding database connection info into views?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, May 15, 2012 at 3:16 PM, Mike Christensen <mike@xxxxxxxxxxxxx> wrote:
>>> 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?

yes, they are called 'tables' :-). stick your connection string in a
table somewhere and do:

create view v as
  select (u).* from dblink((select connstr from yadda where yadda), ...);

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux