top of page
CerebroSQL

Third party PostgreSQL database servers

The node displays information about the created third-party servers in the database  PostgreSQL , as well as detailed information about them

PostgreSQL foreign server

Request

select oid,
      srvname,
      srvtype,
      srvversion,
      srvacl::text,
      srvoptions::text
 from pg_catalog.pg_foreign_server

WRAPPER node

Information about the extension used to work with a third-party server

PostgreSQL foreign server wrapper

select w.oid,
      w.fdwname,
      (select u.usename from pg_catalog.pg_user u where u.usesysid =  w.fdwowner) "owner",
      (select p.proname from pg_catalog.pg_proc p where p.oid =  w.fdwhandler) "proc",
      (select p.proname from pg_catalog.pg_proc p where p.oid =  w.fdwvalidator) "validator",
      w.fdwacl::text,
      w.fdwoptions::text
 from pg_catalog.pg_foreign_data_wrapper w,
      pg_catalog.pg_foreign_server s
where s.oid = $$OIDFOREIGN
  and s.srvfdw = w.oid

TABLE node

Tables using a selected third-party server to display data from a remote database

PostgreSQL foreign server table

SELECT (select (select n.nspname 
                 from pg_catalog.pg_namespace n 
                where n.oid = c.relnamespace )||'.'||relname
        from pg_catalog.pg_class c  
       where c.oid = f.ftrelid ) "table_name",
      ftrelid,
      ftserver,
      ftoptions::text
 FROM pg_foreign_table  f
where ftserver = $$OIDFOREIGN 

MAPPING

Username/password of the third-party database user under which the connection is made

PostgreSQL foreign server mapping

SELECT oid,
      umuser,
      umserver,
      umoptions::text
 FROM pg_user_mapping
where umserver = $$OIDFOREIGN 

bottom of page