Compléments SQL

Si l'extension n'est pas installée il faut utiliser la commande :

CREATE EXTENSION dblink ;

Il se peut que vous rencontriez le message :

ERREUR: n'a pas pu ouvrir le fichier de contrôle d'extension « /usr/share/postgresql/9.3/extension/dblink.control » : Aucun fichier ou dossier de ce type

Voir dans ce cas avec l'administrateur système pour installer les paquets de contributions complémentaires (sudo apt-get install postgresql-contrib).

Une fois le module installé, il est possible d'utiliser dblink_connect pour établir une connexion.

Pour faire un exercice, nous souhaitons faire une requête entre la table production.route_xy de la base droitsXX et la table consultation.commune de la base newdroitXX (créée dans le module 2 sur l'administration de PostgreSQL).

Dans pgAdmin, se connecter à la base droitsXX avec le rôle stageXX.

Exécuter :

select * from dblink_connect('demo','host=localhost user=stageXX password=stageXX dbname=newdroitXX') ;

(demo est le nom de la connexion qui permettra de ne pas renseigner tous les paramètres dans le prochain appel)

nb : On utilise localhost car on est déjà connecté sur le serveur et que l'on souhaite se reconnecter dans une autre base du même serveur.

Le cas échéant il faut préciser le port d'écoute du serveur, si ce n'est pas celui par défaut (5432), exemple :

select * from dblink_connect('demo','host=172.XX.XX.XXX port=5632 user=postgres password=postgres dbname=stage00')

Si vous avez le message OK comme indiqué ci-dessous, la connexion est établie :

Il est maintenant possible de réaliser des requête sur la table distante avec dblink :

Exemple :

WITH toto AS (SELECT * from dblink('demo', 'select insee_comm, geom FROM consultation.commune')

as foo(insee_comm character(5), geom geometry(MultiPolygon, 2154)))

SELECT production.route_xy.*, toto.insee_comm FROM production.route_xy, toto

WHERE st_intersects(production.route_xy.geom, toto.geom);

nb : foo est en gros, l'équivalent de toto en anglais !

Pour se déconnecter on utilisera :

SELECT * FROM dblink_disconnect('demo')

postgres_fdw propose a peu près les mêmes possibilités tout en étant en général plus performant.

si l'extension n'existe pas la créer :

CREATE EXTENSION postgres_fdw ;

Avec le même exemple que pour Dblink on aura  (remplacer XX par votre numéro de stagiaire) :

CREATE SERVER demo FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '172.26.62.50', dbname 'newdroitXX', port '5432') ;

L'ordre SQL peut-être généré sous pgadmin à partir de l'assistant :

puis en entrant les différentes options :

Création d'un utilisateur habilité (remplacer XX par votre numéro de stagiaire):

CREATE USER MAPPING FOR stageXX SERVER demo OPTIONS (user 'stageXX', password 'stageXX') ;

que l'on peut créer par l'assistant sous pgadmin :

Création de la la table distante (il faut donner la liste des colonnes comme pour la création d'une table)

CREATE FOREIGN TABLE commune(geom geometry(MultiPolygon, 2154), nom_comm character varying(50)) SERVER demo OPTIONS (schema_name 'consultation', table_name 'commune') ;

Que l'on peut créer par l'assistant sous pgadmin :

La table apparaît désormais dans la liste des tables distances du schéma public :

Il est maintenant possible d’exécuter une requête comme si la table commune était locale :

SELECT * FROM production.route_xy, commune WHERE st_intersects(production.route_xy.geom, commune.geom);

Pour supprimer la connexion sur une table on utilisera :

DROP FOREIGN TABLE commune ;

pour un utilisateur :

DROP USER MAPPING IF EXISTS FOR geoadmin SERVER demo ;

et pour le serveur (il faut d'abord avoir supprimé les objets liés) :

DROP SERVER demo ;

On peut également utiliser le clic droit > supprimer sur les objets dans pgadmin.