CODEWITHSUNDEEP
postgresqlfunctiondatabase-trigger
Alvarez Santos
Alvarez Santos

Reputation: 21

Postgresql function return trigger

I am having a problem with a trigger. I created a trigger and a function for when performing an INSERT update a field in the same table. Is returning:

Error: function "loss_func" in FROM has return type trigger that is not supported LINE 1: SELECT * FROM table.loss_func ()

Function

CREATE OR REPLACE FUNCTION loss_func()
  RETURNS trigger AS $loss_func$
  BEGIN
     NEW.dt_creation := to_char(now(), 'YYYY-MM-DD');   

     RETURN NULL;
  END;
  $loss_func$ LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION loss_func()
   OWNER TO postgres;

Trigger

CREATE TRIGGER tgr_loss
  AFTER INSERT ON loss
  FOR EACH ROW
  EXECUTE PROCEDURE loss_func();

What am I doing wrong?

Upvotes: 2

Views: 14115

Answers (1)

Akli REGUIG
Akli REGUIG

Reputation: 562

A working version of your code. - The trigger now fires BEFORE insert and updates the value of dt_creation and returns the NEW version of the record : 

drop table loss;

create table loss (
id int ,
dt_created varchar);

CREATE OR REPLACE FUNCTION loss_func()
  RETURNS trigger AS $loss_func$
  BEGIN
     NEW.dt_created := to_char(now(), 'YYYY-MM-DD');   
     RETURN NEW;
  END;
  $loss_func$ LANGUAGE plpgsql VOLATILE
  COST 100;

ALTER FUNCTION loss_func()
   OWNER TO postgres;

CREATE TRIGGER tgr_loss
  BEFORE INSERT ON loss
  FOR EACH ROW
  EXECUTE PROCEDURE loss_func();

insert into loss(id) values(1);

Another solution that i can propose to avoid the usage of a trigger is to use a default value for dt_creation when you create the table (and use timestamp instead of storing the date as varchar) :

...
dt_creation timestamp default now(),
...

or you can alter your table to set the default value to now() : 

alter table loss 
alter column dt_creation set default now();

Upvotes: 3

Related Questions