如何在PostgreSQL中使用dblink跨三个及以上数据库创建视图或查询

Got it, let's walk through how to pull off a join across three or more databases using PostgreSQL's dblink extension—since you already have the hang of querying a single remote DB, this is just a matter of combining multiple dblink results with standard SQL joins. Here's a practical example to show you how it works.

Example Setup #

Let’s say we have three databases we need to connect:

• demodbrnd: Your existing student database with the tbl_student table (from your original example)
• demodb_courses: A database storing course info in tbl_course
• demodb_enrollments: A database tracking which students are enrolled in which courses via tbl_enrollment

We want to build a query that shows each student's name, their enrolled courses, and the course details—pulling data from all three sources.

Full Working Query #

SELECT
  s.RollNo,
  s.Name,
  e.CourseID,
  c.CourseName,
  c.Credits
FROM
  -- Fetch student data from the first remote DB
  public.dblink('demodbrnd', 'select RollNo, Name from public.tbl_student') 
    AS s(RollNo INTEGER, Name CHARACTER VARYING)
JOIN
  -- Fetch enrollment records from the second remote DB, link to students via RollNo
  public.dblink('demodb_enrollments', 'select RollNo, CourseID from public.tbl_enrollment')
    AS e(RollNo INTEGER, CourseID INTEGER)
  ON s.RollNo = e.RollNo
JOIN
  -- Fetch course details from the third remote DB, link to enrollments via CourseID
  public.dblink('demodb_courses', 'select CourseID, CourseName, Credits from public.tbl_course')
    AS c(CourseID INTEGER, CourseName CHARACTER VARYING, Credits INTEGER)
  ON e.CourseID = c.CourseID;

Key Things to Keep in Mind #

• Match Column Types Exactly: The alias you define for each dblink result (like s(RollNo INTEGER...)) must perfectly match the data types of the columns in the remote table. Mismatched types will throw an error.
• Enable dblink First: If you haven’t already, run CREATE EXTENSION IF NOT EXISTS dblink; in your local database to enable the extension.
• Security Best Practice: Avoid hardcoding passwords in your dblink connection strings. Instead, use PostgreSQL's .pgpass file to store credentials, so your connection string can be something like dbname=demodbrnd instead of including username/password directly.

Alternative: Filter Before Joining #

If you need to narrow down results from a remote DB before joining, you can add filters directly in the dblink query. For example, let's only get students enrolled in 2024:

SELECT
  student.RollNo,
  student.Name,
  course.CourseName
FROM
  -- First, get 2024 enrollments from the enrollment DB
  public.dblink(
    'demodb_enrollments',
    'SELECT RollNo, CourseID FROM tbl_enrollment WHERE EnrollDate >= ''2024-01-01'''
  ) AS enroll(RollNo INTEGER, CourseID INTEGER)
-- Join with student data from demodbrnd
JOIN public.dblink('demodbrnd', 'SELECT RollNo, Name FROM tbl_student')
  AS student(RollNo INTEGER, Name CHARACTER VARYING)
  ON enroll.RollNo = student.RollNo
-- Join with course data from demodb_courses
JOIN public.dblink('demodb_courses', 'SELECT CourseID, CourseName FROM tbl_course')
  AS course(CourseID INTEGER, CourseName CHARACTER VARYING)
  ON enroll.CourseID = course.CourseID;

This pattern scales to as many databases as you need—just keep adding JOIN clauses with new dblink calls, as long as you have a common key to link the data together.

内容的提问来源于stack exchange，提问作者Mahendra Sri Dayarathna