CODEWITHSUNDEEP
sqlpostgresql
Nurzhan Nogerbek
Nurzhan Nogerbek

Reputation: 5246

How fix problem "column reference "id" is ambiguous" in PostgreSQL function?

In PostgreSQL database I have 2 table: services and services_organizations_relationship. Each organization has a specific list of services.

My next function need to create new records in services table, then create relationship between services and organizations and finally return list of all new created 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;

When I call this function:

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

I see such error:

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

How to fix this problem?

Upvotes: 0

Views: 3121

Answers (2)

user11882484
user11882484

Reputation:

Try this

GENERATE_SERVICES_ORGANIZATIONS_RELATIONSHIP AS 
        (
            INSERT INTO SERVICES_ORGANIZATIONS_RELATIONSHIP (SERVICE_ID, ORGANIZATION_ID)
            SELECT
                UNNEST(ARRAY_AGG(t1.ID)) AS SERVICE_ID,
                UNNEST(ARRAY[ORGANIZATION_ID_ARRAY]) AS ORGANIZATION_ID
            FROM RESULTS t1
            ON CONFLICT ON CONSTRAINT SERVICES_ORGANIZATIONS_RELATIONSHIP_UNIQUE_KEY DO NOTHING
        )

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 248245

The final line of the query should be

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

To avoid such collisions, you can use different names for the columns in the RETURNS TABLE clause (which are variables) and the columns in the queries (e.g. by using aliases).

Upvotes: 1

Related Questions