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

Question

这是一个中等复杂的CTE，它根据JSON参数的内容更新许多表。当执行这个函数时，Postgres抛出一个column reference "explode" is ambiguous错误。错误所指的行实际上具有限定列引用的表名。

我非常确定是哪一行导致了问题，因为如果我删除下面标记的行，错误就会消失。

我试着解决这个问题已经有一段时间了，但是我不知道。

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;

为什么会有包含此代码的ambiguous column reference error？

Answer 1

问题出在composition CTE中的RETURNING子句。

通过将*与RETURNING一起使用，它将返回from_list中所有表的所有列。

returning子句应该指定要返回的列和表，特别是在from_list中有多个表的情况下。

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

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
  )