I know that in Oracle it's possible to create stored dblink and after that use it in query. For example:
Script for creation dblink:
CREATE PUBLIC DATABASE LINK my_link CONNECT TO my_schema IDENTIFIED BY shema_password USING 'remote';
And after that we can use it in our queries:
SELECT * FROM some_table@my_link;
I didn't find same solution for Postgres. I undestood that we can create named dblink connection:
For this we must use dblink_connect with name param. But created named dblink will destroy after session close.
Or we can create dblink connection for every queries:
SELECT *
FROM dblink('host= port= dbname= user= password=',
'select table_schema, table_name from information_schema.tables where table_schema = ''data''') AS t1 (table_schema TEXT, table_name TEXT);
Is it possible create stored dblink in Postgres and use it in different queries? Or I should create some function that return dblink connection params which encapsulate them?
I try use foreign table and do next steps:
Create postgres_fdw extension:
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
Create Server:
CREATE SERVER my_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '-', port '-', dbname '-');
And create mapping for user 'sys' where set remote user and password:
CREATE USER MAPPING FOR sys SERVER light_house OPTIONS ( USER 'remote_user', PASSWORD 'remove_password');
GRANT USAGE ON FOREIGN SERVER my_server TO sys;
Create foreign table in schema:
CREATE FOREIGN TABLE system.my_local_table (
colums ..
) SERVER my_server OPTIONS (schema_name 'remote_user', table_name 'remote_table'
);
GRANT SELECT ON TABLE system.home_measurement TO argus_sys;
after that I catch next exception:
[2F003] ERROR: password is required
Description: Non-superuser cannot connect if the server does not request a password.
Help: Target server's authentication method must be changed.
