CODEWITHSUNDEEP
sqlpostgresqlplpgsqldatabase-trigger
Mike.S
Mike.S

Reputation: 47

Function returns bad value inside of a trigger

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

Upvotes: 1

Views: 95

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

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?

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

Upvotes: 1

Related Questions