Postgres, plpgsql: Is there a way to connect to other DB from inside of a stored procedure?
I have two DB's one is f开发者_运维知识库eed by filtered data from another, now i'm using perl script witch executes query on foreign DB, stores a result in a csv file, and loads it to local DB using \COPY sytnatx
Is there a way to write plpgsql function witch will connect to foreign DB and load filtered data in local DB ( I know it can be done in ie. plperl, but i search more "native" way )
And there is the DBI-LINK that supports much more databases :)
Currently, PostgreSQL has dblink, but it only supports connecting to other PostgreSQL instances - not any other database, sadly.
I would recommend PL/Proxy, which is significantly easier to use - just write the desired stored procedure on the target database (with some minor caveats, like not using enumerated types), and declare the same function on the source, PL/Proxy will handle the communications. It is the basis for Skype's distributed database architecture and is production-ready.
精彩评论