CODEWITHSUNDEEP
postgresqltriggers
Ngoc Dinh Cong
Ngoc Dinh Cong

Reputation: 87

Postgresql: UPDATE before INSERT function

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()

Upvotes: 4

Views: 1394

Answers (2)

Karol Samborski
Karol Samborski

Reputation: 2965

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;

Upvotes: 2

Kaushik Nayak
Kaushik Nayak

Reputation: 31736

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

Upvotes: 4

Related Questions