Postgres Function Trigger Sequence Dependency Error

Question

I have the following functions and trigger with sequence setup:

I want to create a function and trigger that anytime I add a new row to STRATEGY_SITES table, the 'SITE_NUM' field will have the new sequential number from SITENUM_SEQ. Schema name is gismgr.

I am getting the following error:Underlying DBMS error[Error:control reached end of trigger procedure without return Context: PL/pgSQL function process_sites_edit()(gismgr.strategy_sites)::SQLSTATE=2F005][gismgr.startegy_sites]

CREATE OR REPLACE FUNCTION process_sites_edit() RETURNS TRIGGER AS $SITE_EDIT_TRIGGER$
   begin
            new.SITE_NUM := nextval('gismgr.SITENUM_SEQ');
end;
$SITE_EDIT_TRIGGER$ LANGUAGE 'plpgsql';

create TRIGGER SITE_EDIT_TRIGGER
before insert or update on STRATEGY_SITES for each row
EXECUTE PROCEDURE process_strategy_sites_edit();

CREATE SEQUENCE gismgr."SITENUM_SEQ" owned by gismgr.strategy_Sites.site_num
    INCREMENT 1
    START 19080
    MINVALUE 19080
    MAXVALUE 9999999999999999
    CACHE 20;

Can you share error?

Ahmet Sina Ustem
– Ahmet Sina Ustem

2020-02-22 02:01:33 +00:00
Commented Feb 22, 2020 at 2:01 — Ahmet Sina Ustem
– Ahmet Sina Ustem, Commented Feb 22, 2020 at 2:01
@AhmetSinaUstem edited my original post- see error

sanc_mn
– sanc_mn

2020-02-22 04:14:04 +00:00
Commented Feb 22, 2020 at 4:14 — sanc_mn
– sanc_mn, Commented Feb 22, 2020 at 4:14

Belayer · Accepted Answer · 2020-02-22 21:56:50Z

0

This seems to be an ORACLEism which is unnecessary in Postgres. Assuming your table already exsists then just

alter table *table_name* alter column site_num default nextval('gismgr.SITENUM_SEQ')

Also make sure the insert does not mention the site_num column. If you feel you must continue with the trigger approach the your trigger function needs to specify the return value.

CREATE OR REPLACE FUNCTION process_sites_edit() 
RETURNS TRIGGER AS $SITE_EDIT_TRIGGER$
begin
     new.SITE_NUM := nextval('gismgr.SITENUM_SEQ');
     return new;
end;
$SITE_EDIT_TRIGGER$ LANGUAGE 'plpgsql';

I would also suggest you do not want to fire the trigger on updates. That will change the site number on any/every update of a given row Are there FK referencing it - they will not be updated the update would fail. Further the procedure executed must match the function name:

create TRIGGER SITE_EDIT_TRIGGER
before insert on STRATEGY_SITES for each row
EXECUTE PROCEDURE process_sites_edit();

answered Feb 22, 2020 at 21:56

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sanc_mn Over a year ago

thanks Belayer. I tried with alter table alter column but for an already existing field with data populated will it work? I added a new row and the sequence is not working. It got it working with function and trigger though. What will be a good syntax if I want to update adding the following to the syntax: Begin if new.site_num is Null then old.site_num:=new.site_num; end if. Adding the following to the trigger: *create TRIGGER SITE_EDIT_TRIGGER before insert or update on STRATEGY_SITES for each row EXECUTE PROCEDURE process_sites_edit(); Will that work?

Belayer Over a year ago

I'm not sure what you mean by "good syntax if I want to update". Update what? I assume it refers to site_num. In that case what is the purpose of the site_num column?

sanc_mn Over a year ago

Sorry for the confusion. The above query fixed the issue - with function, trigger and sequence. I was seeing the problem in the application where users are entering new rows. But it was configuration issue and not trigger. Thank you

Collectives™ on Stack Overflow

Postgres Function Trigger Sequence Dependency Error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related