MySQL迁移PostgreSQL：让函数返回与MySQL存储过程一致的行集

方案1：返回与MySQL完全一致的关系型结果集 #

直接让PostgreSQL函数返回连接查询后的普通行数据，和MySQL存储过程的行为完全匹配，NodeJS调用后可直接通过row[0]['t2_t1_id']访问列数据。

使用SQL函数实现（无需PL/pgSQL，更简洁）：

CREATE OR REPLACE FUNCTION getData()
RETURNS SETOF RECORD
LANGUAGE sql
AS $$
SELECT t1.*,
       t2.*,
       t3.*,
       t4.*
FROM  t1
JOIN  t2 ON t1.t1_id = t2.t2_t1_id
JOIN  t3 ON t1.t1_id = t3.t3_t1_id
JOIN  t4 ON t1.t1_id = t4.t4_t1_id;
$$;

如果需要明确指定返回列的结构（避免客户端解析问题），可以列出所有列（假设已知表结构）：

CREATE OR REPLACE FUNCTION getData()
RETURNS TABLE(
  t1_id INT,
  t1_name VARCHAR,
  -- 依次列出t1、t2、t3、t4的所有列
  t2_id INT,
  t2_t1_id INT,
  ...
)
LANGUAGE sql
AS $$
SELECT t1.*,
       t2.*,
       t3.*,
       t4.*
FROM  t1
JOIN  t2 ON t1.t1_id = t2.t2_t1_id
JOIN  t3 ON t1.t1_id = t3.t3_t1_id
JOIN  t4 ON t1.t1_id = t4.t4_t1_id;
$$;

NodeJS中调用时，使用SELECT * FROM getData()即可拿到和MySQL CALL getData()完全一致的行列数组。

方案2：返回平级JSON数组（若需JSON格式） #

如果业务需要返回JSON，但要匹配原MySQL的列结构（无外层getdata包裹），可以让函数直接返回一个JSON数组，每个元素对应一行数据：

CREATE OR REPLACE FUNCTION getData()
RETURNS JSON
LANGUAGE sql
AS $$
SELECT json_agg(row_to_json(rd))
FROM (
  SELECT t1.*,
         t2.*,
         t3.*,
         t4.*
  FROM  t1
  JOIN  t2 ON t1.t1_id = t2.t2_t1_id
  JOIN  t3 ON t1.t1_id = t3.t3_t1_id
  JOIN  t4 ON t1.t1_id = t4.t4_t1_id
) rd;
$$;

该函数返回的JSON结构示例：

[
  {
    "t1_id": 62,
    "t1_name": "xx",
    "t2_id": 67,
    "t2_t1_id": 62,
    ...
  },
  {
    "t1_id": 63,
    "t1_name": "yy",
    "t2_id": 69,
    "t2_t1_id": 63,
    ...
  }
]

NodeJS调用后解析该JSON数组，即可通过row[0]['t2_t1_id']访问目标列，和原逻辑一致。

原问题原因说明 #

你之前的PostgreSQL函数返回TABLE(j JSON)，导致每行数据被包装在名为j的列中，客户端驱动可能将函数名getdata作为外层键，生成了嵌套的{getdata: {...}}结构。上述两种方案分别从返回关系型结果集、调整JSON生成方式两个角度解决了这个问题。

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