Function returns bad value inside of a trigger

Question

I have two functions that return the good value. But when I call those functions inside of a trigger they always returns 0 instead of the good value.

The return type of those functions is real. The direct and dramatic consequence is that the trigger inserts wrong values in tables when it is called.

The function:

create or replace function get_remaining_hour(id_user_v integer,id_absence_v_type integer,id_year_v integer) returns real as
$BODY$
BEGIN
    return (select sum(number_hour) 
            from remaining_absence_day 
            where id_user= $1 
              and id_absence_type=$2 
              and id_year=$3 );
END;
$BODY$
LANGUAGE 'plpgsql' ;

The trigger function (modified for testing!):

create  OR REPLACE  function update_absence() returns TRIGGER  AS
$BODY$
DECLARE
  old_number_hour real;
BEGIN
  old_number_hour:=get_remaining_hour(3,2,8);
  insert into debugging(col,val) values('old_number_hour', old_number_hour);
  return null;
END;
$BODY$
LANGUAGE 'plpgsql' ;

The trigger definition:

drop trigger if exists update_absence on absence;
CREATE  TRIGGER update_absence
    after update of type,duration_hour,duration_day on absence
    for each ROW
    execute procedure update_absence();

You are not changing anything in the trigger function. You are only assigning that value to a local variable, but you do not change the `NEW` record. — , Aug 30 '16 at 09:42
Your trigger does nothing. What is a purpose of assignment to `old_number_hour`? This variable is not visible outside the function. — klin, Aug 30 '16 at 09:42
it's an extract of the code, the part wich is not functionnal. When i use old_number_hour to update or insert in any table from the trigger the result is always 0. — Mike.S, Aug 30 '16 at 09:46
In fact, the value of old_number_hour is used with two othe variable to caculate the to putt in a field of table — Mike.S, Aug 30 '16 at 09:54
The trigger function as shown will **never** change anything in the database. You need to show us the **complete** code. — , Aug 30 '16 at 09:58
i have add in the code an exemple wheni use the function to insert into a table — Mike.S, Aug 30 '16 at 10:10
Are you saying that you don't see the correct values in the table `debugging`? — , Aug 30 '16 at 10:12
yes instead of the correct for five or 6 the only value i see in the table debugging is 0. — Mike.S, Aug 30 '16 at 10:18

score 1 · Accepted Answer · edited May 23 '17 at 10:29

The presented code should work.

It is particularly odd that you see 0 as result. If no matching row is found in remaining_absence_day, you would see NULL, not 0. But if you call the function with the same parameters in the same environment you should see the same result to begin with.

The remaining possible explanation I can think of: confusion with the schema search path. Like: you have a second instance of the function get_remaining_hour() or the table remaining_absence_day in a different schema. And you call the function with a different setting for search_path. Did you run your comparison in the same session?

How does the search_path influence identifier resolution and the "current schema"

Or, since you work with an AFTER trigger: there might be other triggers on table absence that modify the table remaining_absence_day, which are fired before your trigger.

All other modifications I made are of cosmetic nature or minor simplifications.

CREATE OR REPLACE FUNCTION get_remaining_hour(id_user_v int
                                            , id_absence_v_type int
                                            , id_year_v int)
  RETURNS real AS
$func$
BEGIN
   RETURN (
   SELECT sum(number_hour) 
   FROM   remaining_absence_day  -- referencing the right table? see search_path
   WHERE  id_user         = $1 
   AND    id_absence_type = $2 
   AND    id_year         = $3
   );
END
$func$  LANGUAGE plpgsql STABLE;  -- don't quote the language name

CREATE OR REPLACE FUNCTION update_absence()
  RETURNS TRIGGER AS
$func$
BEGIN   
   INSERT INTO debugging(col, val)
   VALUES('old_number_hour', get_remaining_hour(3,2,8));  -- hard coded only for testing?
   RETURN null;  -- only good for AFTER trigger
END
$func$  LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_absence ON absence;
CREATE TRIGGER update_absence
AFTER UPDATE OF type, duration_hour, duration_day ON absence
FOR EACH ROW EXECUTE PROCEDURE update_absence();

Thank you very much, you were right when you said: "Or, since you work with an AFTER trigger: there might be other triggers on table absence that modify the table remaining_absence_day, which are fired before your trigger." There was e trigger fired on table debugging which interfered with my new trigger. Thank you everbody for your help. — Mike.S, Aug 30 '16 at 14:46

Function returns bad value inside of a trigger

1 Answers1