Postgresql function return trigger

Question

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?

SELECT * FROM table.loss_func () why are you doing this ? what are you trying to select with this? this is what causes the error. Remove it and it will try to create your trigger. The execution stopped at the first line — Akli REGUIG
– Akli REGUIG, Commented Dec 4, 2017 at 19:14
AFTER triggers can't return NULL. (can't alter values either) — Jasen
– Jasen, Commented Dec 8, 2017 at 5:11

Akli REGUIG · Accepted Answer · 2017-12-04 20:51:13Z

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

Collectives™ on Stack Overflow

Postgresql function return trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related