CODEWITHSUNDEEP
sqlpostgresqltriggerspostgresql-9.5
Timogavk
Timogavk

Reputation: 869

Why postgres trigger function returns some columns reference is ambiguous?

I have two tables: Source table where I insert or update value

CREATE TABLE test.table_1
(
    id smallint NOT NULL DEFAULT nextval('test.table_1_id_seq'::regclass),
    sub_id smallint,
    value numeric(5,5)
)

TABLESPACE pg_default;

ALTER TABLE test.table_1
    OWNER to postgres;

I need to get difference between NEW and OLD value and add it to sum_value in table_1_sum on update. Or need to insert new row if id and sub_id already exists in table_1_sum

CREATE TABLE test.table_1_sum
(
    id smallint,
    sub_id smallint,
    sum_value numeric(5,5),
    CONSTRAINT unq_constraint UNIQUE (id, sub_id)
)

TABLESPACE pg_default;

ALTER TABLE test.table_1_sum
    OWNER to postgres;

I made a function with trigger to do this

CREATE FUNCTION test.add_diff()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
        delta_value   numeric;      
    BEGIN      
        IF (TG_OP = 'UPDATE') THEN            
            delta_value = NEW.value - OLD.value;        
        ELSIF (TG_OP = 'INSERT') THEN       
            delta_value = NEW.value;        
        ELSIF (TG_OP = 'DELETE') THEN       
            delta_value = -1 * OLD.value;       
        END IF;         
            INSERT INTO test.table_1_sum (
                sub_id,
                sum_value
                )
            VALUES (
                NEW.sub_id,
                NEW.value               
               )
                ON CONFLICT ON CONSTRAINT unq_constraint
                DO UPDATE 
                    SET 
                    sum_value = sum_value + delta_value;    
        RETURN NULL;
    END;
$BODY$;
ALTER FUNCTION test.add_diff()
    OWNER TO postgres;
    
    
CREATE TRIGGER add_value_diff
    AFTER INSERT OR DELETE OR UPDATE 
    ON test.table_1
    FOR EACH ROW
    EXECUTE PROCEDURE test.add_diff();

It does not work. I have no idea why.

It returns an error:

ERROR: column reference "sum_value" is ambiguous LINE 13: sum_value = sum_value + delta_value

Where I'm wrong?

Upvotes: 0

Views: 309

Answers (2)

Pavel Stehule
Pavel Stehule

Reputation: 45770

There is "virtual" table named EXCLUDED that holds an row with values that should be inserted (when INSERT has ON CONFLICT DO clause). This virtual table has same structure (same columns) like target table, so the just name of column is not unique.

You can read more details in related Postgres documentation.

Upvotes: 1

S-Man
S-Man

Reputation: 23676

demo:db<>fiddle

You have to explicitly reference table_1_sum in the calculation:

sum_value = table_1_sum.sum_value + delta_value;

Upvotes: 1

Related Questions