如何在不创建函数的情况下以事务形式执行PostgreSQL函数逻辑？

我有一个PostgreSQL存储函数：

CREATE FUNCTION schema.myfunction(id uuid)
RETURNS TABLE (report jsonb) AS $$
  BEGIN
  RETURN QUERY WITH rows AS (
    SELECT
      jsonb_build_object(
      'rowNumber', ROW_NUMBER(),
      'id', t.id
    ) AS row
  FROM schema.table t
  WHERE t.id = id
  )
  SELECT
    COALESCE(jsonb_agg(r.row), '[]') AS report
  FROM rows r;
  END;
  $$ LANGUAGE plpgsql STRICT STABLE SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION schema.myfunction(uuid) TO schema_user;

我希望不创建该函数，直接以事务形式执行其逻辑并得到相同结果，类似如下形式：

db.transaction(async (trx) => {
      const {
        rows: [result],
      } = await trx.raw(
        '<supposed code here, maybe>;',
        [id]
      );

      res.status(200).json(result);
})

我尝试过使用DO块，但无法从中返回结果，恳请提供方向指引。

完全可行，DO块确实无法返回结果，因为它是匿名过程，仅用于执行无返回值的操作。你只需要把原函数中的核心SQL逻辑提取出来，作为参数化查询直接执行即可。

核心SQL提取与调整 #

原函数的核心逻辑是构建JSON数组的查询，直接提取后调整参数占位符（PostgreSQL默认用$1作为位置占位符）：

WITH rows AS (
  SELECT
    jsonb_build_object(
      'rowNumber', ROW_NUMBER() OVER (), -- 补充窗口子句确保语法合法
      'id', t.id
    ) AS row
  FROM schema.table t
  WHERE t.id = $1 -- 用占位符替代原函数的参数
)
SELECT
  COALESCE(jsonb_agg(r.row), '[]') AS report
FROM rows r;

在事务中执行的代码示例 #

将上述SQL代入你的事务代码中即可：

db.transaction(async (trx) => {
  const {
    rows: [result],
  } = await trx.raw(
    `WITH rows AS (
      SELECT
        jsonb_build_object(
          'rowNumber', ROW_NUMBER() OVER (),
          'id', t.id
        ) AS row
      FROM schema.table t
      WHERE t.id = $1
    )
    SELECT COALESCE(jsonb_agg(r.row), '[]') AS report FROM rows r;`,
    [id]
  );

  res.status(200).json(result);
})

注意事项 #

• STRICT模式匹配：原函数是严格模式（参数为NULL时直接返回NULL），你需要在应用层处理参数为NULL的情况，或者在SQL中添加WHERE $1 IS NOT NULL来对齐逻辑。
• STABLE属性兼容：该属性表示函数结果在事务内稳定，直接执行SQL天然满足这一点。
• SECURITY DEFINER权限处理：原函数以创建者权限执行，如果你的应用用户schema_user没有直接访问schema.table的SELECT权限，直接执行SQL会报错。这种情况下，要么给schema_user授予对应表的SELECT权限，要么在执行SQL前切换到函数所有者角色（需具备相应权限）。

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