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 Feb 22 '20 at 02:01 — Ahmet Sina Ustem, Feb 22 '20 at 02:01
@AhmetSinaUstem edited my original post- see error – sanc_mn Feb 22 '20 at 04:14 — sanc_mn, Feb 22 '20 at 04:14

score 0 · Accepted Answer · answered Feb 22 '20 at 21:56

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 '20 at 21:56

Belayer

13,578
2
11
22

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? – sanc_mn Feb 23 '20 at 15:55
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? – Belayer Feb 23 '20 at 16:03
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 – sanc_mn Feb 24 '20 at 17:03

Postgres Function Trigger Sequence Dependency Error

1 Answers1