PL/pgSQL声明表变量查询报错：relation 'organization_peers'不存在

问题描述 #

编写的PL/pgSQL代码执行时提示错误：relation "organization_peers" does not exist，但已经声明了该变量，代码如下：

DECLARE
    prev_port INT;
    org_id INT;
    port INT;
    organization_peers networking.peers;
BEGIN
    SELECT o.id INTO org_id
    FROM networking.peers p
    LEFT JOIN networking.peer_service_endlets pse ON p.id = pse.peer_id
    LEFT JOIN networking.service_endlets se ON se.id = pse.service_endlet_id
    LEFT JOIN networking.services s ON s.id = se.service_id
    LEFT JOIN billing.organizations o ON o.id = s.organization_id
    WHERE p.id = NEW.id;

    SELECT * INTO organization_peers -- 此处为变量赋值
    FROM networking.peers p
    LEFT JOIN networking.peer_service_endlets pse ON p.id = pse.peer_id
    LEFT JOIN networking.service_endlets se ON se.id = pse.service_endlet_id
    LEFT JOIN networking.services s ON s.id = se.service_id
    LEFT JOIN billing.organizations o ON o.id = s.organization_id
    WHERE o.id = org_id AND p.vwan_port IS NOT NULL
    ORDER BY p.vwan_port ASC;

    SELECT p.vwan_port INTO prev_port
    FROM organization_peers -- 此处报错
    LIMIT 1;

    --...

原因分析 #

你声明的organization_peers是单行记录类型（对应networking.peers表的单条记录结构），但在后续查询中把它当成了表/结果集来使用。PL/pgSQL不支持直接从单行记录变量中执行FROM查询，因此数据库会把它当成数据库中的物理表名去查找，自然找不到对应的关系表。

另外，你的SELECT * INTO organization_peers语句存在隐患：如果查询返回多条记录，会直接抛出too many rows的错误，因为单行记录变量只能存储一条记录。

解决方法 #

根据你的需求，有几种可行的调整方案：

方案1：直接获取目标值（仅需第一条记录的vwan_port） #

如果只是需要第一条记录的vwan_port，完全不需要存储整个结果集，直接合并查询逻辑：

DECLARE
    prev_port INT;
    org_id INT;
    port INT;
BEGIN
    SELECT o.id INTO org_id
    FROM networking.peers p
    LEFT JOIN networking.peer_service_endlets pse ON p.id = pse.peer_id
    LEFT JOIN networking.service_endlets se ON se.id = pse.service_endlet_id
    LEFT JOIN networking.services s ON s.id = se.service_id
    LEFT JOIN billing.organizations o ON o.id = s.organization_id
    WHERE p.id = NEW.id;

    -- 直接获取排序后的第一条vwan_port
    SELECT p.vwan_port INTO prev_port
    FROM networking.peers p
    LEFT JOIN networking.peer_service_endlets pse ON p.id = pse.peer_id
    LEFT JOIN networking.service_endlets se ON se.id = pse.service_endlet_id
    LEFT JOIN networking.services s ON s.id = se.service_id
    LEFT JOIN billing.organizations o ON o.id = s.organization_id
    WHERE o.id = org_id AND p.vwan_port IS NOT NULL
    ORDER BY p.vwan_port ASC
    LIMIT 1;

    --...

方案2：使用数组存储多条记录 #

如果后续需要用到整个结果集，将变量声明为数组类型，通过array_agg聚合记录，查询时用unnest展开：

DECLARE
    prev_port INT;
    org_id INT;
    port INT;
    organization_peers networking.peers[]; -- 改为数组类型
BEGIN
    SELECT o.id INTO org_id
    FROM networking.peers p
    LEFT JOIN networking.peer_service_endlets pse ON p.id = pse.peer_id
    LEFT JOIN networking.service_endlets se ON se.id = pse.service_endlet_id
    LEFT JOIN networking.services s ON s.id = se.service_id
    LEFT JOIN billing.organizations o ON o.id = s.organization_id
    WHERE p.id = NEW.id;

    -- 用array_agg聚合多条记录到数组
    SELECT array_agg(p) INTO organization_peers
    FROM networking.peers p
    LEFT JOIN networking.peer_service_endlets pse ON p.id = pse.peer_id
    LEFT JOIN networking.service_endlets se ON se.id = pse.service_endlet_id
    LEFT JOIN networking.services s ON s.id = se.service_id
    LEFT JOIN billing.organizations o ON o.id = s.organization_id
    WHERE o.id = org_id AND p.vwan_port IS NOT NULL
    ORDER BY p.vwan_port ASC;

    -- 用unnest展开数组查询
    SELECT p.vwan_port INTO prev_port
    FROM unnest(organization_peers) p
    LIMIT 1;

    --...

方案3：使用临时表存储结果集 #

如果结果集较大或需要多次查询，用临时表替代变量：

DECLARE
    prev_port INT;
    org_id INT;
    port INT;
BEGIN
    SELECT o.id INTO org_id
    FROM networking.peers p
    LEFT JOIN networking.peer_service_endlets pse ON p.id = pse.peer_id
    LEFT JOIN networking.service_endlets se ON se.id = pse.service_endlet_id
    LEFT JOIN networking.services s ON s.id = se.service_id
    LEFT JOIN billing.organizations o ON o.id = s.organization_id
    WHERE p.id = NEW.id;

    -- 创建临时表存储结果集
    CREATE TEMP TABLE organization_peers AS
    SELECT p.*
    FROM networking.peers p
    LEFT JOIN networking.peer_service_endlets pse ON p.id = pse.peer_id
    LEFT JOIN networking.service_endlets se ON se.id = pse.service_endlet_id
    LEFT JOIN networking.services s ON s.id = se.service_id
    LEFT JOIN billing.organizations o ON o.id = s.organization_id
    WHERE o.id = org_id AND p.vwan_port IS NOT NULL
    ORDER BY p.vwan_port ASC;

    -- 直接从临时表查询
    SELECT vwan_port INTO prev_port
    FROM organization_peers
    LIMIT 1;

    --...
    -- 临时表会在会话结束后自动清理，也可以手动DROP
    DROP TABLE IF EXISTS organization_peers;
END;

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