当使用表名限定时，Postgres列引用不明确

Question

这是一个中等复杂的CTE，它根据JSON参数的内容更新许多表。执行此函数时，Postgres抛出一个列引用“explode”是不明确的错误。错误所指的行实际上有一个表名来限定列引用。

我很确定我是对的，因为如果我删除下面标记的行，错误就会消失。

我一直在想这个问题，但我不知道。

CREATE OR REPLACE FUNCTION prd.update_composition (json, OUT result json) AS
$$
BEGIN
  -- Update and return the new composition
  WITH composition AS (
    UPDATE prd.composition SET (
      explode
    ) = (
      payload_composition.explode
    )
    FROM json_to_record($1) AS payload_composition (
      id      integer,
      explode boolean
    )
    WHERE id IS NOT NULL
    RETURNING *
  ), payload_component AS (
    SELECT
      composition.composition_id,
      component."productId" AS product_id,
      component.quantity,
      component.removed
    FROM json_to_recordset($1->'components') AS component (
      "productId" integer,
      quantity    numeric(10,3),
      removed     boolean
    )
    CROSS JOIN composition
  ), updated_component AS (
    UPDATE prd.component existing SET (
      product_id,
      quantity
    ) = (
      component.product_id,
      component.quantity
    )
    FROM (
      SELECT
        composition_id,
        product_id,
        quantity
      FROM payload_component
      WHERE removed IS NOT TRUE
    ) component
    WHERE existing.composition_id = component.composition_id
    RETURNING *
  ), deleted AS (
    DELETE FROM prd.component existing
    USING (
      SELECT
        composition_id,
        product_id
      FROM payload_component
      WHERE removed IS TRUE
    ) payload_deleted
    WHERE existing.composition_id = payload_deleted.composition_id
      AND existing.product_id = payload_deleted.product_id
  )
  SELECT json_strip_nulls(to_json(r)) INTO result
  FROM (
    SELECT
      composition.composition_id,
      composition.explode -- HERE IS THE OFFENDING LINE --
    FROM composition
  ) r;
END
$$
LANGUAGE 'plpgsql' SECURITY DEFINER;

为什么此代码存在不明确的列引用错误？

Answer 1

问题在于组合CTE中的返回子句。

通过使用*withPREURning，它返回from_list中所有表的所有列。

返回子句应该指定要返回哪些列和从哪些表返回，特别是如果from_list中有多个表。

在这种情况下，应在插入中使用别名来引用所需的列：

WITH composition AS (
    UPDATE prd.composition c SET ( -- See here
      explode
    ) = (
      payload_composition.explode
    )
    FROM json_to_record($1) AS payload_composition (
      id      integer,
      explode boolean
    )
    WHERE id IS NOT NULL
    RETURNING c.* -- and here
  )