Postgresql column reference is ambiguous-CodePudding

I want to call my function but I get this error:

ERROR: column reference "list" is ambiguous LINE 3: SET list = ARRAY_APPEND(list, input_list2),

the error is on the second list inside array_append function.

My function:

CREATE OR REPLACE FUNCTION update_order(input_id uuid,input_sku text,input_store_id uuid,input_order_date bigint,input_asin text,input_amount int,input_list text[],input_price real,input_list2 text) RETURNS void LANGUAGE plpgsql AS
$body$
#variable_conflict use_column
BEGIN
    INSERT INTO orders_summary(id,sku,store_id,order_date,asin,amount,list,price)
            VALUES(input_id,input_sku,input_store_id,to_timestamp(input_order_date / 1000.0),input_asin,input_amount,input_list,input_price) ON CONFLICT(sku,order_date) DO UPDATE
              SET list = ARRAY_APPEND(list, input_list2),
                  amount = amount   input_amount,
                  price = input_price
              WHERE NOT list @> input_list;
END
$body$;

CodePudding user response：

You have to use the alias name in the insert query because list has two references, one reference in EXCLUDED.list and another reference to the column for an update statement.

Please check the below query (I append the alias with name os in query):

CREATE OR REPLACE FUNCTION update_order(input_id uuid,input_sku text,input_store_id uuid,input_order_date bigint,input_asin text,input_amount int,input_list text[],input_price real,input_list2 text) RETURNS void LANGUAGE plpgsql AS
$body$
#variable_conflict use_column
BEGIN
    INSERT INTO orders_summary as os (id,sku,store_id,order_date,asin,amount,list,price)
            VALUES(input_id,input_sku,input_store_id,to_timestamp(input_order_date / 1000.0),input_asin,input_amount,input_list,input_price) ON CONFLICT(sku,order_date) DO UPDATE
              SET list = ARRAY_APPEND(os.list, input_list2),
                  amount = os.amount   input_amount,
                  price = input_price
              WHERE NOT os.list @> input_list;
END
$body$;

Or you can use table name:

CREATE OR REPLACE FUNCTION update_order(input_id uuid,input_sku text,input_store_id uuid,input_order_date bigint,input_asin text,input_amount int,input_list text[],input_price real,input_list2 text) RETURNS void LANGUAGE plpgsql AS
$body$
#variable_conflict use_column
BEGIN
    INSERT INTO orders_summary (id,sku,store_id,order_date,asin,amount,list,price)
            VALUES(input_id,input_sku,input_store_id,to_timestamp(input_order_date / 1000.0),input_asin,input_amount,input_list,input_price) ON CONFLICT(sku,order_date) DO UPDATE
              SET list = ARRAY_APPEND(orders_summary.list, input_list2),
                  amount = orders_summary.amount   input_amount,
                  price = input_price
              WHERE NOT orders_summary.list @> input_list;
END
$body$;