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

4

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

edited Jan 15, 2019 at 10:52

answered Jan 15, 2019 at 10:47

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

1 Comment

Ngoc Dinh Cong Over a year ago

Very easy to understand

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

2

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;

answered Jan 15, 2019 at 11:23

Karol Samborski

3,0001 gold badge15 silver badges21 bronze badges

Collectives™ on Stack Overflow

Postgresql: UPDATE before INSERT function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related