Postgres多对多关系中查询路径及关联站点的技术问询

PATHS 表 #

STOPS 表 #

PATH_STOP 表（多对多关联表） #

路径（PATHS）与站点（STOPS）为多对多关系：一条路径对应多个站点，单个站点也可属于多条路径。现需通过单条查询获取路径信息的同时带回关联站点，已编写的PL/pgSQL函数仅完成部分逻辑：

create or replace function get_paths() returns setof paths as $$ 
  declare
    p paths[]
  begin
    select * into p from paths;
    -- not sure how to move on from here.
  end;
$$ language plpgsql;

解决方案 #

方案1：直接SQL查询（无需函数）

通过JOIN结合聚合函数，可一次性返回路径及关联站点：

SELECT
  p.uid,
  p.name,
  p.duration,
  json_agg(s) AS stops
FROM paths p
LEFT JOIN path_stop ps ON p.uid = ps.path
LEFT JOIN stops s ON ps.stop = s.uid
GROUP BY p.uid, p.name, p.duration;

• LEFT JOIN确保无关联站点的路径也会被返回
• json_agg(s)将路径对应的所有站点打包为JSON数组，方便应用层处理

若需结构化数组而非JSON，可替换为array_agg：

SELECT
  p.uid,
  p.name,
  p.duration,
  array_agg(s) AS stops
FROM paths p
LEFT JOIN path_stop ps ON p.uid = ps.path
LEFT JOIN stops s ON ps.stop = s.uid
GROUP BY p.uid, p.name, p.duration;

方案2：改进PL/pgSQL函数

原函数返回类型仅包含路径结构，无法携带站点信息，需调整返回类型：

方式A：基于自定义复合类型返回

先创建复合类型：

CREATE TYPE path_with_stops AS (
  uid uuid,
  name text,
  duration numeric,
  stops stops[]
);

再编写函数：

CREATE OR REPLACE FUNCTION get_paths() 
RETURNS SETOF path_with_stops AS $$
BEGIN
  RETURN QUERY
    SELECT
      p.uid,
      p.name,
      p.duration,
      array_agg(s) AS stops
    FROM paths p
    LEFT JOIN path_stop ps ON p.uid = ps.path
    LEFT JOIN stops s ON ps.stop = s.uid
    GROUP BY p.uid, p.name, p.duration;
END;
$$ LANGUAGE plpgsql;

方式B：直接返回TABLE类型

无需提前创建复合类型，直接在函数中定义返回结构：

CREATE OR REPLACE FUNCTION get_paths() 
RETURNS TABLE (
  uid uuid,
  name text,
  duration numeric,
  stops stops[]
) AS $$
BEGIN
  RETURN QUERY
    SELECT
      p.uid,
      p.name,
      p.duration,
      array_agg(s) AS stops
    FROM paths p
    LEFT JOIN path_stop ps ON p.uid = ps.path
    LEFT JOIN stops s ON ps.stop = s.uid
    GROUP BY p.uid, p.name, p.duration;
END;
$$ LANGUAGE plpgsql;

调用函数：

SELECT * FROM get_paths();

内容的提问来源于stack exchange，提问作者Daniele