sql - 如何解决PostgreSQL函数中 "column reference "id"is ambiguous"的问题？

Question

在 PostgreSQL 数据库中，我有 2 个表:services 和 services_organizations_relationship。每个组织都有一个特定的服务列表。

我的下一个函数需要在 services 表中创建新记录，然后在服务和组织之间创建关系，最后返回所有新创建服务的列表。

CREATE OR REPLACE FUNCTION test (
    SERVICE_NAME_ARRAY VARCHAR[],
    ACTIVE_ARRAY BOOLEAN[],
    DESCRIPTION_ARRAY TEXT[],
    ORGANIZATION_ID_ARRAY INT[]
) RETURNS TABLE (
    ID UUID,
    NAME VARCHAR,
    ACTIVE BOOLEAN,
    DESCRIPTION TEXT
) AS $$
    BEGIN
        RETURN QUERY
        WITH RESULTS AS (
            INSERT INTO SERVICES (NAME, ACTIVE, DESCRIPTION) 
            SELECT 
                UNNEST(ARRAY[SERVICE_NAME_ARRAY]) AS NAME,
                UNNEST(ARRAY[ACTIVE_ARRAY]) AS ACTICE,
                UNNEST(ARRAY[DESCRIPTION_ARRAY]) AS DESCRIPTION
            RETURNING ID, NAME, ACTIVE, DESCRIPTION
        ),
        GENERATE_SERVICES_ORGANIZATIONS_RELATIONSHIP AS 
        (
            INSERT INTO SERVICES_ORGANIZATIONS_RELATIONSHIP (SERVICE_ID, ORGANIZATION_ID)
            SELECT
                UNNEST(ARRAY_AGG(ID)) AS SERVICE_ID,
                UNNEST(ARRAY[ORGANIZATION_ID_ARRAY]) AS ORGANIZATION_ID
            FROM RESULTS
            ON CONFLICT ON CONSTRAINT SERVICES_ORGANIZATIONS_RELATIONSHIP_UNIQUE_KEY DO NOTHING
        )
        SELECT ID, NAME, ACTIVE, DESCRIPTION FROM RESULTS;
    END;
$$ LANGUAGE plpgsql;

当我调用这个函数时:

SELECT * FROM test(ARRAY['SLOT', 'JTC'], ARRAY[TRUE, FALSE], ARRAY['SLOT', 'JTC'], ARRAY[30572, 30573]);

我看到这样的错误:

SQL Error [42702]: ERROR: column reference "id" is ambiguous
Details: It could refer to either a PL/pgSQL variable or a table column.
Where: PL/pgSQL function test(character varying[],boolean[],text[],integer[]) line 3 at RETURN QUERY

如何解决这个问题？

Answer 1

查询的最后一行应该是

SELECT result.id, result.name,... FROM result

为避免此类冲突，您可以为 RETURNS TABLE 子句中的列(它们是变量)和查询中的列使用不同的名称(例如，通过使用别名)。