Skip to main content
Ctrl+K

Tantor Special Edition 16.8 documentation

Table of contents:

  • Preface
    • 1.  What Is Tantor SE?
    • 2. A Brief History of PostgreSQL
    • 3. What are the differences between Tantor SE 16 and PostgreSQL 16
    • 4. Conventions
    • 5. Bug Reporting Guidelines
  • Part I. Tutorial
    • Chapter 1. Synopsis
    • Chapter 2. The SQL Language
    • Chapter 3. Advanced Features
  • Part II. The SQL Language
    • Chapter 4. SQL Syntax
    • Chapter 5. Data Definition
    • Chapter 6. Data Manipulation
    • Chapter 7. Queries
    • Chapter 8. Data Types
    • Chapter 9. Functions and Operators
    • Chapter 10. Type Conversion
    • Chapter 11. Indexes
    • Chapter 12. Full Text Search
    • Chapter 13. Concurrency Control
    • Chapter 14. Performance Tips
    • Chapter 15. Parallel Query
    • Chapter 16. Autonomous Transactions
  • Part III. Server Administration
    • Chapter 17. Installation from Binaries
    • Chapter 18. Server Setup and Operation
    • Chapter 19. Server Configuration
    • Chapter 20. Client Authentication
    • Chapter 21. Database Roles
    • Chapter 22. Managing Databases
    • Chapter 23. Localization
    • Chapter 24. Routine Database Maintenance Tasks
    • Chapter 25. Backup and Restore
    • Chapter 26. High Availability, Load Balancing, and Replication
    • Chapter 27. Monitoring Database Activity
    • Chapter 28. Monitoring Disk Usage
    • Chapter 29. Reliability and the Write-Ahead Log
    • Chapter 30. Logical Replication
    • Chapter 31. Just-in-Time Compilation (JIT)
  • Part IV. Client Interfaces
    • Chapter 32. libpq — C Library
    • Chapter 33. Large Objects
    • Chapter 34. ECPG — Embedded SQL in C
    • Chapter 35. The Information Schema
  • Part V. Server Programming
    • Chapter 36. Extending SQL
    • Chapter 37. Triggers
    • Chapter 38. Event Triggers
    • Chapter 39. The Rule System
    • Chapter 40. Procedural Languages
    • Chapter 41. PL/pgSQL — SQL Procedural Language
    • Chapter 42. PL/Tcl — Tcl Procedural Language
    • Chapter 43. PL/Perl — Perl Procedural Language
    • Chapter 44. PL/Python — Python Procedural Language
    • Chapter 45. Server Programming Interface
    • Chapter 46. Background Worker Processes
    • Chapter 47. Logical Decoding
    • Chapter 48. Replication Progress Tracking
    • Chapter 49. Archive Modules
  • Part VI. Reference
    • SQL Commands
    • PostgreSQL Client Applications
    • PostgreSQL Server Applications
  • Part VII. Internals
    • Chapter 50. Overview of PostgreSQL Internals
    • Chapter 51. System Catalogs
    • Chapter 52. System Views
    • Chapter 53. Frontend/Backend Protocol
    • Chapter 54. PostgreSQL Coding Conventions
    • Chapter 55. Native Language Support
    • Chapter 56. Writing a Procedural Language Handler
    • Chapter 57. Writing a Foreign Data Wrapper
    • Chapter 58. Writing a Table Sampling Method
    • Chapter 59. Writing a Custom Scan Provider
    • Chapter 60. Genetic Query Optimizer
    • Chapter 61. Table Access Method Interface Definition
    • Chapter 62. Index Access Method Interface Definition
    • Chapter 63. Generic WAL Records
    • Chapter 64. Custom WAL Resource Managers
    • Chapter 65. B-Tree Indexes
    • Chapter 66. GiST Indexes
    • Chapter 67. SP-GiST Indexes
    • Chapter 68. GIN Indexes
    • Chapter 69. BRIN Indexes
    • Chapter 70. Hash Indexes
    • Chapter 71. Database Physical Storage
    • Chapter 72. Transaction Processing
    • Chapter 73. System Catalog Declarations and Initial Contents
    • Chapter 74. How the Planner Uses Statistics
    • Chapter 75. Backup Manifest Format
  • Part VIII. Appendixes
    • Appendix A. Tantor SE Error Codes
    • Appendix B. Date/Time Support
    • Appendix C. SQL Key Words
    • Appendix D. SQL Conformance
    • Appendix E. Release Notes
    • Appendix F. Additional Supplied Modules and Extensions
    • Appendix G. Additional Supplied Programs
    • Appendix H. Additional External Modules
    • Appendix I. External Projects
    • Appendix J. Tantor SE Limits
    • Appendix K. Acronyms
    • Appendix L. Glossary
    • Appendix M. Color Support
    • Appendix N. Obsolete or Renamed Features
  • Bibliography
  • Index

dblink_connect

dblink_connect#

dblink_connect
dblink_connect
Prev UpF.14. dblink — connect to other PostgreSQL databasesHome Next

dblink_connect

dblink_connect — opens a persistent connection to a remote database

Synopsis

dblink_connect(text connstr) returns text
dblink_connect(text connname, text connstr) returns text

Description

dblink_connect() establishes a connection to a remote Tantor SE database. The server and database to be contacted are identified through a standard libpq connection string. Optionally, a name can be assigned to the connection. Multiple named connections can be open at once, but only one unnamed connection is permitted at a time. The connection will persist until closed or until the database session is ended.

The connection string may also be the name of an existing foreign server. It is recommended to use the foreign-data wrapper dblink_fdw when defining the foreign server. See the example below, as well as CREATE SERVER and CREATE USER MAPPING.

Arguments

connname

The name to use for this connection; if omitted, an unnamed connection is opened, replacing any existing unnamed connection.

connstr

libpq-style connection info string, for example hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd options=-csearch_path=. For details see Section 32.1.1. Alternatively, the name of a foreign server.

Return Value

Returns status, which is always OK (since any error causes the function to throw an error instead of returning).

Notes

If untrusted users have access to a database that has not adopted a secure schema usage pattern, begin each session by removing publicly-writable schemas from search_path. One could, for example, add options=-csearch_path= to connstr. This consideration is not specific to dblink; it applies to every interface for executing arbitrary SQL commands.

Only superusers may use dblink_connect to create non-password-authenticated and non-GSSAPI-authenticated connections. If non-superusers need this capability, use dblink_connect_u instead.

It is unwise to choose connection names that contain equal signs, as this opens a risk of confusion with connection info strings in other dblink functions.

Examples

SELECT dblink_connect('dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

SELECT dblink_connect('myconn', 'dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

-- FOREIGN DATA WRAPPER functionality
-- Note: local connection must require password authentication for this to work properly
--       Otherwise, you will receive the following error from dblink_connect():
--       ERROR:  password is required
--       DETAIL:  Non-superuser cannot connect if the server does not request a password.
--       HINT:  Target server's authentication method must be changed.

CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'contrib_regression');

CREATE USER regress_dblink_user WITH PASSWORD 'secret';
CREATE USER MAPPING FOR regress_dblink_user SERVER fdtest OPTIONS (user 'regress_dblink_user', password 'secret');
GRANT USAGE ON FOREIGN SERVER fdtest TO regress_dblink_user;
GRANT SELECT ON TABLE foo TO regress_dblink_user;

\set ORIGINAL_USER :USER
\c - regress_dblink_user
SELECT dblink_connect('myconn', 'fdtest');
 dblink_connect
----------------
 OK
(1 row)

SELECT * FROM dblink('myconn', 'SELECT * FROM foo') AS t(a int, b text, c text[]);
 a  | b |       c
----+---+---------------
  0 | a | {a0,b0,c0}
  1 | b | {a1,b1,c1}
  2 | c | {a2,b2,c2}
  3 | d | {a3,b3,c3}
  4 | e | {a4,b4,c4}
  5 | f | {a5,b5,c5}
  6 | g | {a6,b6,c6}
  7 | h | {a7,b7,c7}
  8 | i | {a8,b8,c8}
  9 | j | {a9,b9,c9}
 10 | k | {a10,b10,c10}
(11 rows)

\c - :ORIGINAL_USER
REVOKE USAGE ON FOREIGN SERVER fdtest FROM regress_dblink_user;
REVOKE SELECT ON TABLE foo FROM regress_dblink_user;
DROP USER MAPPING FOR regress_dblink_user SERVER fdtest;
DROP USER regress_dblink_user;
DROP SERVER fdtest;

Prev Up Next
F.14. dblink — connect to other PostgreSQL databases Home dblink_connect_u
Commercial use is possible only with written permission from the company LLC “Tantor Labs”
© LLC "Tantor Labs"
  • Technical Support Policy
  • Cookie Processing Policy
  • Website User Agreement
Products
  • Tantor DBMS
  • Tantor Platform
  • Documentation
Contacts
  • +7 495 369-48-16
  • info@tantorlabs.ru
16.8.1 - 8e8befb1 - 2025-04-10 15:46:39