Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

SQL error in pgagent.pga_exception_trigger #2

Open
nhajek opened this Issue May 2, 2012 · 0 comments

Comments

Projects
None yet
1 participant

nhajek commented May 2, 2012

On PG 9.1 an SQL error is generated because the local variable is the same name as a column in a query, the following code corrects the issue.

CREATE OR REPLACE FUNCTION pgagent.pga_exception_trigger (
)
RETURNS trigger AS
$body$
DECLARE

l_jobid int4 := 0;

BEGIN

 IF TG_OP = 'DELETE' THEN

    SELECT INTO l_jobid jscjobid FROM pgagent.pga_schedule WHERE jscid = OLD.jexscid;

    -- update pga_job from remaining schedules
    -- the actual calculation of jobnextrun will be performed in the trigger
    UPDATE pgagent.pga_job
       SET jobnextrun = NULL
     WHERE jobenabled AND jobid=l_jobid;
    RETURN OLD;
ELSE

    SELECT INTO l_jobid jscjobid FROM pgagent.pga_schedule WHERE jscid = NEW.jexscid;

    UPDATE pgagent.pga_job
       SET jobnextrun = NULL
     WHERE jobenabled AND jobid=l_jobid;
    RETURN NEW;
END IF;

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment