CODEWITHSUNDEEP
sqlpostgresqltriggers
jsbroks
jsbroks

Reputation: 550

Set trigger NEW to returning value of UPDATE

I am trying to update a increment counter inside a trigger and assign it to NEW.

CREATE OR REPLACE FUNCTION public.set_export_increment_and_update_schema()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    VOLATILE
AS $BODY$BEGIN

NEW.increment := UPDATE export_schema
    SET "exportsIncrement" = "exportsIncrement" + 1
    WHERE id = NEW."schemaId" 
    RETURNING "exportsIncrement";

RETURN NEW;
END;
$BODY$;

However, I am getting a syntax error. If I run the update query alone, it works fine but once I try assigning it to the new this error appears.

ERROR: syntax error at or near "SET"
LINE 9: SET "exportsIncrement" = "exportsIncrement" + 1

Upvotes: 0

Views: 32

Answers (1)

Belayer
Belayer

Reputation: 14932

Your syntax for returning a value from an update is totally incorrect. You do not/can not use assignment statement as you are trying. Instead you use returning... into ... 

create or replace function public.set_export_increment_and_update_schema()
    returns trigger
    language 'plpgsql'
    volatile
as $body$
begin

 update export_schema
    set "exportsincrement" = "exportsincrement" + 1
    where id = new."schemaid" 
    returning "exportsincrement" into new.increment;

return new;
end;
$body$;

Upvotes: 2

Related Questions