Postgresql: UPDATE before INSERT function

Question

I have problem when create function for trigger. I want to UPDATE inserted value BEFORE INSERT data to DB. My code look like this:

CREATE OR REPLACE FUNCTION test_func() RETURNS TRIGGER AS $$ DECLARE cnt INTEGER; BEGIN cnt := COUNT(*) FROM sample_tbl WHERE id = NEW.id AND created_date = NEW.created_date; NEW.current_order := cnt + 1; // I want to set value of sample_tbl.current_order automatically END $$ LANGUAGE plpgsql; CREATE TRIGGER test_trigger BEFORE INSERT ON test_tbl FOR EACH ROW EXECUTE PROCEDURE test_func();

I inserted data then IDE said: control reached end of trigger procedure without RETURN Where: PL/pgSQL function test_func()

Kaushik Nayak · Accepted Answer · 2019-01-15 10:52:51Z

The error says that you must return something from the Trigger ( either NEW or NULL )

There's no Trigger needed for this. A simple View using this select query will give you the required result

--create or replace view sample_view as select t.id, t.created_date, row_number() OVER ( partition by id,created_date order by id ) as current_order FROM sample_tbl t;

This will exactly match the records if updated using a Trigger

CREATE OR REPLACE FUNCTION test_func() RETURNS TRIGGER AS $$ DECLARE cnt INTEGER; BEGIN select COUNT(*) INTO cnt FROM sample_tbl WHERE id = NEW.id AND created_date = NEW.created_date; NEW.current_order := cnt + 1; RETURN NEW; --required END $$ LANGUAGE plpgsql;

Demo

Karol Samborski · Accepted Answer · 2019-01-15 11:23:42Z

Your trigger function is just missing RETURN NEW; statement:

CREATE OR REPLACE FUNCTION test_func() RETURNS TRIGGER AS $$ DECLARE cnt INTEGER; BEGIN cnt := COUNT(*) FROM sample_tbl WHERE id = NEW.id AND created_date = NEW.created_date; NEW.current_order := cnt + 1; RETURN NEW; END $$ LANGUAGE plpgsql;

Collectives™ on Stack Overflow

Postgresql: UPDATE before INSERT function

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related