CODEWITHSUNDEEP
databasepostgresqltriggersplpgsql
theuserkaps
theuserkaps

Reputation: 308

how to fix the end of procedure trigger error

is there a way to fix this error or there is something i am missing in my code,im learning postgresql and i have been working on table inheritance and triggers i have two tables the temporary_object table and the persons table the persons table inherits all the properties the the temporary object has,then i have created a trigger function on the persons table which checks the records with the same id before updating the tables my problem comes when i try to run the insert query,im getting these errors 

ERROR: end of trigger procedure achieved without RETURN
CONTEXT: function PL / pgSQL muli_function ()

ERROR: end of trigger procedure achieved without RETURN
SQL-state: 2F005
Context: The PL / pgSQL muli_function ()

this is by insert query

INSERT INTO persons (id, time_create, time_dead, First_name, Last_name) values (1, '2011-10-07 15:25:00 EDT', '2011-10-07 3:25 PM EDT', 'sathiya', 'james');

and this my trigger function and trigger itself

CREATE FUNCTION muli_function() RETURNS trigger AS '
BEGIN
  IF tg_op = ''UPDATE'' THEN
     UPDATE persons
     SET time_dead = NEW.time_create
     Where
         id = NEW.id
         AND time_dead IS NULL
         ;
  RETURN new;
  END IF;
END
' LANGUAGE plpgsql;

CREATE TRIGGER sofgr BEFORE INSERT OR DELETE OR UPDATE
        ON persons FOR each ROW
        EXECUTE PROCEDURE muli_function();

and these are my two tables query 

CREATE TABLE temporary_object
(
  id integer NOT NULL,
  time_create timestamp without time zone NOT NULL,
  time_dead timestamp without time zone,
  PRIMARY KEY (id, time_create)
);

CREATE TABLE persons
(

  First_Name text,
  Last_Name text
)
INHERITS (temporary_object);

Upvotes: 1

Views: 2389

Answers (2)

András Váczi
András Váczi

Reputation: 3002

You define the trigger procedure for an INSERT OR UPDATE trigger, but don't provide a case for the INSERT (just as mu is too short is commented), only the UPDATE. So the trigger executes the procedure for an INSERT, then it does not find anything to execute and exits without a RETURN, which is obviously an error.

Upvotes: 1

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28531

Try this:

CREATE FUNCTION muli_function() RETURNS trigger AS '
BEGIN
  IF tg_op = ''UPDATE'' THEN
     UPDATE persons
     SET time_dead = NEW.time_create
     Where
         id = NEW.id
         AND time_dead IS NULL
         ;

  END IF;
  RETURN new;
END
' LANGUAGE plpgsql;

UPD Better something like this:

CREATE FUNCTION muli_function() RETURNS trigger AS '
BEGIN
  IF tg_op = ''UPDATE'' THEN
    IF NEW.time_dead IS NULL THEN
     NEW.time_dead = NEW.time_create
    END IF;      
  END IF;
  RETURN new;
END
' LANGUAGE plpgsql;

Upvotes: 2

Related Questions