CODEWITHSUNDEEP
postgresqlstored-proceduresdatabase-trigger
kartoos khan
kartoos khan

Reputation: 449

Postgres error [42883] and [42601] while executing stored procedure from trigger

I want to create a trigger which will execute a stores procedure which will update few rows. Procedure works when I test it using Call method but the trigger can not find it with error that function does not exists. Here is my procedure 

create or replace PROCEDURE update_reg_location(latitude text, longitude text,userid int)
LANGUAGE SQL
AS $$
update users set reg_lat=latitude , reg_lon=longitude where id =userid
$$;

When I use 

call update_reg_location('123','234',1)

from IDE its works fine and record is updated but when i use it in trigger it wont compile.

CREATE TRIGGER update_reg_location
    after INSERT ON users
    FOR EACH ROW
    EXECUTE PROCEDURE update_reg_location('123','234',1);

Also getting error when I want to get new value of the inserted row like this

CREATE TRIGGER update_reg_location
    after INSERT ON users
    FOR EACH ROW
    EXECUTE PROCEDURE update_reg_location(new.lat,new.lon,1);

getting error as syntax error at or near new. (new.lat,new.lon,1); in this line

Upvotes: 0

Views: 608

Answers (1)

user330315
user330315

Reputation:

You don't need to pass parameters to the procedure and you don't need to use UPDATE if you use a BEFORE trigger, you can simply assign the values.

Trigger functions also need to use PL/pgSQL, you can't write them in SQL.

create or replace function update_reg_location()
  returns trigger
LANGUAGE plpgsql
AS $$
begin
  new.reg_lat = new.lat;
  new.reg_lon = new.lon;
  return new;
end;
$$;

Then define a BEFORE trigger:

CREATE TRIGGER update_reg_location
    BEFORE INSERT ON users
    FOR EACH ROW
    EXECUTE PROCEDURE update_reg_location();

Upvotes: 1

Related Questions