Dblink postgresql что это

Dblink postgresql что это

dblink — выполняет запрос в удалённой базе данных

Синтаксис

dblink(text connname, text sql [, bool fail_on_error]) returns setof record dblink(text connstr, text sql [, bool fail_on_error]) returns setof record dblink(text sql [, bool fail_on_error]) returns setof record

Описание

dblink выполняет запрос (обычно SELECT , но это может быть и любой другой оператор SQL, возвращающий строки) в удалённой базе данных.

Когда этой функции передаются два аргумента типа text , первый сначала рассматривается как имя постоянного подключения; если такое подключение находится, команда выполняется для него. Если не находится, первый аргумент воспринимается как строка подключения, как для функции dblink_connect , и заданное подключение устанавливается только на время выполнения этой команды.

Аргументы

Имя используемого подключения; опустите этот параметр, чтобы использовать безымянное подключение. connstr

Строка подключения, описанная ранее для dblink_connect sql

SQL-запрос, который вы хотите выполнить в удалённой базе данных, например select * from foo . fail_on_error

Если равен true (это значение по умолчанию), в случае ошибки, выданной на удалённой стороне соединения, ошибка также выдаётся локально. Если равен false, удалённая ошибка выдаётся локально как ЗАМЕЧАНИЕ, и функция не возвращает строки.

Возвращаемое значение

Эта функция возвращает строки, выдаваемые в результате запроса. Так как dblink может выполнять произвольные запросы, она объявлена как возвращающая тип record , а не некоторый определённый набор столбцов. Это означает, что вы должны указать ожидаемый набор столбцов в вызывающем запросе — в противном случае PostgreSQL не будет знать, чего ожидать. Например:

SELECT * FROM dblink('dbname=mydb options=-csearch_path=', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';

В части « псевдонима » предложения FROM должны указываться имена столбцов и типы, которые будет возвращать функция. (Указание имён столбцов в псевдониме таблицы предусмотрено стандартом SQL, но определение типов столбцов является расширением PostgreSQL .) Это позволяет системе понять, во что должно разворачиваться обозначение * , и на что ссылается proname в предложении WHERE , прежде чем пытаться выполнять эту функцию. Во время выполнения произойдёт ошибка, если действительный результат запроса из удалённой базы данных не будет содержать столько столбцов, сколько указано в предложении FROM . Однако имена столбцов могут не совпадать, так же, как dblink не настаивает на точном совпадении типов. Функция завершится успешно, если возвращаемые строки данных будут допустимыми для ввода в тип столбца, объявленный в предложении FROM .

Замечания

Использовать dblink с предопределёнными запросами будет удобнее, если создать представление. Это позволит скрыть в его определении информацию о типах столбцов и не выписывать её в каждом запросе. Например:

CREATE VIEW myremote_pg_proc AS SELECT * FROM dblink('dbname=postgres options=-csearch_path=', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text); SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';

Примеры

SELECT * FROM dblink('dbname=postgres options=-csearch_path=', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; proname | prosrc ------------+------------ byteacat | byteacat byteaeq | byteaeq bytealt | bytealt byteale | byteale byteagt | byteagt byteage | byteage byteane | byteane byteacmp | byteacmp bytealike | bytealike byteanlike | byteanlike byteain | byteain byteaout | byteaout (12 rows) SELECT dblink_connect('dbname=postgres options=-csearch_path='); dblink_connect ---------------- OK (1 row) SELECT * FROM dblink('select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; proname | prosrc ------------+------------ byteacat | byteacat byteaeq | byteaeq bytealt | bytealt byteale | byteale byteagt | byteagt byteage | byteage byteane | byteane byteacmp | byteacmp bytealike | bytealike byteanlike | byteanlike byteain | byteain byteaout | byteaout (12 rows) SELECT dblink_connect('myconn', 'dbname=regression options=-csearch_path='); dblink_connect ---------------- OK (1 row) SELECT * FROM dblink('myconn', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; proname | prosrc ------------+------------ bytearecv | bytearecv byteasend | byteasend byteale | byteale byteagt | byteagt byteage | byteage byteane | byteane byteacmp | byteacmp bytealike | bytealike byteanlike | byteanlike byteacat | byteacat byteaeq | byteaeq bytealt | bytealt byteain | byteain byteaout | byteaout (14 rows)

Пред.	Наверх	След.
dblink_disconnect	Начало	dblink_exec

WTFM.INFO

Write The F* Manual — Заметки о сетях, администрировании и вообще

PostgreSQL выполнение запросов к нескольким базам

В PostgreSQL не существует способа создать запрос к базам данных отличным от текущей (https://wiki.postgresql.org/), но выполнять запросы между базами возможно при помощи расширения dblink (https://www.postgresql.org/docs/9.6/static/dblink.html).

Допустим, есть машина с CentOS 7, установленным PostgreSQL 9.6 и ip адресом 192.168.1.10.
Для начала необходимо чтобы был пользователь с правами подключения к нашему серверу PostgreSQL по паролю (метод md5). Для примера используем пользователя postgres и настроим права подключения в конфиге pg_hba.conf:
host all postgres 192.168.1.0/24 md5
После этого применим изменения:
sudo su — postgres
/usr/pgsql-9.6/bin/pg_ctl reload
Либо при помощи SQL запроса:
SELECT pg_reload_conf();
Теперь установим пакет contrib (если он еще не установлен) нужной нам версии, содержащий в себе расширения для PostgreSQL:
yum install postgresql96-contrib
После этого в директории (в зависимости от ОС директория может отличаться) /usr/pgsql-9.6/share/extension/ можно будет увидеть файлы расширений PostgreSQL.
Для установки dblink достаточно выполнить SQL запрос:
CREATE EXTENSION dblink;
После этого расширение установлено, можно его использовать.
Важно: команда CREATE EXTENSION dblink; установит расширение только для текущей бд (в моем случае в бд postgres), выполнять запросы с использованием dblink нужно подключившись к бд, в которой оно установлено. Если вы подключаетесь к другой бд и выполняете запросы из нее — необходимо сначала установить расширение в этой бд. В данном случае dblink установлен в бд postgres.
Допустим, есть две базы db_one и db_two с идентичными по структуре таблицами tbl_employee (db_one) и tbl_employee2 (db_two), необходимо скопировать данные из таблицы первой бд во вторую.
Создаем первую бд и заполним таблицу данными:
CREATE DATABASE db_one;

CREATE TABLE tbl_employee
(
EmpID INT PRIMARY KEY
,EmpName VARCHAR
,EmpGender CHAR(1)
);

INSERT INTO tbl_employee
VALUES
(1,’Anvesh’,’M’)
,(2,’Neevan’,’M’)
,(3,’Martin’,’M’);
Создаем вторую бд с пустой таблицей:
CREATE DATABASE db_two;

CREATE TABLE tbl_employee2
(
EmpID INT PRIMARY KEY
,EmpName VARCHAR
,EmpGender CHAR(1)
);
Переключаемся на бд postgres и проверяем возможность подключения к бд db_one при помощи dblink:
\c postgres
SELECT dblink_connect(‘host=192.168.1.10 port=5432 user=postgres password=pass dbname=db_one’);
При успешном подключении в ответ получим:
dblink_connect
—————-
OK
(1 row)
Подключимся обратно к БД postgres и пробуем сделать выборку данных из таблицы первой бд:
\c postgres
SELECT * FROM dblink(‘host=192.168.1.10 port=5432 user=postgres password=pass dbname=db_one’, ‘SELECT * FROM tbl_employee’) AS tbl(id int, name varchar, gender char(1));
В ответ получим:
id | name | gender
—-+———+———
1 | Anvesh | M
2 | Neevan | M
3 | Martin | M
(3 rows)
Подключаемся к бд db_two, устанавливаем расширение dblink и копируем данные из таблицы tbl_employee бд db_one:
\c db_two
CREATE EXTENSION dblink;
INSERT INTO tbl_employee2 SELECT * FROM dblink(‘host=192.168.1.10 port=5432 user=postgres password=pass dbname=db_one’, ‘SELECT * FROM tbl_employee’) AS tbl(id int, name varchar, gender char(1));
Проверяем что данные скопированы из таблицы tbl_employee (бд db_one) в таблицу tbl_employee2 (бд db_two):
SELECT * FROM tbl_employee2;
В ответ получаем:
empid | empname | empgender
——-+———+————
1 | Anvesh | M
2 | Neevan | M
3 | Martin | M
(3 rows)

dblink#

dblink выполняет запрос (обычно SELECT , но это может быть любой SQL-оператор, возвращающий строки) в удаленной базе данных.

Когда заданы два аргумента text , первый из них сначала ищется как имя постоянного соединения; если найдено, то команда выполняется на этом соединении. Если не найдено, то первый аргумент рассматривается как строка информации о соединении, как для dblink_connect , и указанное соединение устанавливается только на время выполнения этой команды.

Аргументы

Имя соединения, которое нужно использовать; опустите этот параметр, чтобы использовать безымянное соединение.

Строка с информацией о подключении, как описано ранее для функции dblink_connect .

SQL-запрос, который вы хотите выполнить в удаленной базе данных, например select * from foo .

Если значение true (по умолчанию, если не указано), то ошибка, возникшая на удаленной стороне соединения, также вызывает ошибку на локальной стороне. Если значение false, то удаленная ошибка сообщается локально как NOTICE, и функция не возвращает ни одной строки.

Возвращаемое значение

Функция возвращает строку(и), созданную запросом. Поскольку dblink может использоваться с любым запросом, он объявлен как возвращающий record , а не указывающий какой-либо конкретный набор столбцов. Это означает, что вы должны указать ожидаемый набор столбцов в вызывающем запросе — в противном случае Tantor SE-1C не будет знать, что ожидать. Вот пример:

SELECT * FROM dblink('dbname=mydb options=-csearch_path=', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';

Часть FROM предложения “ alias ” должна указывать имена столбцов и типы, которые функция вернет. (Указание имен столбцов в псевдониме на самом деле является стандартным синтаксисом SQL, но указание типов столбцов является расширением Tantor SE-1C ). Это позволяет системе понять, что должно быть развернуто вместо * и на что ссылается proname в предложении WHERE , до попытки выполнить функцию. Во время выполнения будет сгенерирована ошибка, если фактический результат запроса из удаленной базы данных не имеет того же количества столбцов, что и в предложении FROM . Однако имена столбцов не обязательно должны совпадать, и dblink не настаивает на точном соответствии типов. Он будет успешным, если возвращаемые строки данных являются допустимым вводом для типа столбца, объявленного в предложении FROM .

Примечания

Удобный способ использования dblink с предопределенными запросами — создание представления. Это позволяет информацию о типе столбца замаскировать в представлении, вместо того, чтобы каждый раз указывать ее в каждом запросе. Например,

CREATE VIEW myremote_pg_proc AS SELECT * FROM dblink('dbname=postgres options=-csearch_path=', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text); SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';

Примеры

SELECT * FROM dblink('dbname=postgres options=-csearch_path=', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; proname | prosrc ------------+------------ byteacat | byteacat byteaeq | byteaeq bytealt | bytealt byteale | byteale byteagt | byteagt byteage | byteage byteane | byteane byteacmp | byteacmp bytealike | bytealike byteanlike | byteanlike byteain | byteain byteaout | byteaout (12 rows) SELECT dblink_connect('dbname=postgres options=-csearch_path='); dblink_connect ---------------- OK (1 row) SELECT * FROM dblink('select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; proname | prosrc ------------+------------ byteacat | byteacat byteaeq | byteaeq bytealt | bytealt byteale | byteale byteagt | byteagt byteage | byteage byteane | byteane byteacmp | byteacmp bytealike | bytealike byteanlike | byteanlike byteain | byteain byteaout | byteaout (12 rows) SELECT dblink_connect('myconn', 'dbname=regression options=-csearch_path='); dblink_connect ---------------- OK (1 row) SELECT * FROM dblink('myconn', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; proname | prosrc ------------+------------ bytearecv | bytearecv byteasend | byteasend byteale | byteale byteagt | byteagt byteage | byteage byteane | byteane byteacmp | byteacmp bytealike | bytealike byteanlike | byteanlike byteacat | byteacat byteaeq | byteaeq bytealt | bytealt byteain | byteain byteaout | byteaout (14 rows)

Назад	Наверх	Далее
dblink_disconnect	Начало	dblink_exec

GIS-LAB

PostgreSQL: удаленные источники данных средствами dblink

Дежин Александр, 19.08.2010

Недавно в этом блоге вы могли наблюдать заметку о публикации данных в формате PostGIS, в том числе и данных OSM. Конечно, это не отменяет публикуемых shape-файлов, однако скачивать их при каждом обновлении может быть не всегда удобно, особенно если вам нужен какой-либо специфический срез этих данных. Поэтому хотелось бы рассказать о одном, как мне кажется, довольно интересном способе получения этих данных встроенными средствами PostgreSQL и PostGIS.

Самый очевидный способ, получить копию таблицы – это воспользоваться pg_dump -t , а затем pg_restore и зарегистрировать колонку геометрий – однако это скучно и не интересно, да и фильтры там наложить негде.

Для PostgreSQL есть расширение dblink, которое позволяет выполнять запросы к удаленным базам данных непосредственно из SQL, не прибегая к помощи внешних скриптов.

Чтобы включить это расширение нужно посмотреть папку, в которую у вас были установлены расширения (точный путь может зависеть от дистрибутива), и выполнить dblink.sql из этой папки на вашей БД. Например, так:

То есть процедура установки, очень похожа на установку PostGIS. После этого, можно напрямую обращаться к удаленным базам из своих SQL-скриптов, например, так:

SELECT * FROM dblink('dbname=osm host=gis-lab.info user=guest password=guest', 'SELECT osm_id, waterway, name, way FROM osm_line WHERE waterway IN (''river'', ''stream'')' ) AS rivers(osm_id int, tag varchar(50), name varchar(200), way geometry);

• ‘dbname=osm host=gis-lab.info user=guest password=guest’ – это строка подключения к удаленной базе
• ‘SELECT osm_id, waterway, name, way FROM osm_line WHERE . ‘ – запрос, который вы хотите выполнить (escape-последовательность для одинарных кавычек – » )
• rivers(osm_id int, tag varchar(50), name varchar(200), way geometry) – псевдоним в текущем запросе и описание полей с их типами

При необходимости выбрать данные по охвату, можно поступить например так:

SELECT * FROM dblink('dbname=osm host=gis-lab.info user=guest password=guest', 'SELECT osm_id, name, way FROM osm_point WHERE place IN (''city'',''town'') AND way && ST_SetSRID(ST_MakeBox2D(ST_Point(37.32, 56.53), ST_Point(41.20, 58.96)), 4326)' ) AS rivers(osm_id int, name varchar(50), geom geometry);

Где, ST_SetSRID(ST_MakeBox2D(ST_Point(37.32, 56.53), ST_Point(41.20, 58.96)), 4326) – охват ярославской области, а && – оператор пересечения. Таким образом можно выбрать все крупные населенный пункты ярославской области, обозначенные как place=city|town.

Если по каким-то причинам вам не удобно указывать строку соединения каждый раз, то можно воспользоваться dblink_connect для того чтобы создать подключение и в дальнейшем его использовать.

Поделиться ссылкой:

• Click to share on Twitter (Opens in new window)
• Click to share on Facebook (Opens in new window)
• Click to share on Google+ (Opens in new window)