CODEWITHSUNDEEP
postgresqltriggers
COVID-19
COVID-19

Reputation: 11

Error Message: Control reached end of procedure without RETURN

I have a nice table in POSTGRES:

CREATE TABLE public.pasajeros
(
    direccion_residencia character varying COLLATE pg_catalog."default",
    nombre character varying COLLATE pg_catalog."default",
    pasajero_id integer NOT NULL DEFAULT nextval('pasajeros_pasajero_id_seq'::regclass),
    fecha_nacimiento date,
    CONSTRAINT pasajeros_pkey PRIMARY KEY (pasajero_id)
)

I tried to add a trigger every time a new pasajero is inserted, a table stores the new value of total registers, so I created a new table:

CREATE TABLE public.cont_pasajeros
(
    total integer,
    tiempo time with time zone,
    id_t integer NOT NULL DEFAULT nextval('cont_pasajeros_id_t_seq'::regclass),
    CONSTRAINT cont_pasajeros_pkey PRIMARY KEY (id_t)
)

Then I created a new function to be includen in the trigger:

DECLARE
    count_ integer :=0;
BEGIN
    count_ := (SELECT count (*) FROM pasajeros);
    RAISE NOTICE 'number of registers %', count_;
    INSERT INTO cont_pasajeros (total,tiempo) VALUES (count_, now()); 
END

Then I created the trigger 'in perse':

CREATE TRIGGER trigger_ 
AFTER INSERT ON pasajeros
FOR EACH ROW 
EXECUTE PROCEDURE my_func();

The problem occured when I tried to add a new tuple into the table 'pasajeros':

INSERT INTO public.pasajeros(
    direccion_residencia, nombre, fecha_nacimiento)
    VALUES ('calle 1 a', 'benito', '2000-05-01');

a error occurred:

Error Message: Control reached end of procedure without RETURN

What I did wrong? apparently everything is normal. I am using pgAdmin4

Upvotes: 0

Views: 79

Answers (1)

COVID-19
COVID-19

Reputation: 11

This is the solution I found: I modified the code of the function to include the sstatement RETURN NULL.

  1. I drop the function:

    DROP FUNCTION public.my_func();

  2. execute the new code, to include the sentence RETURN NULL

    CREATE FUNCTION public.my_func()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    count_ integer :=0;
BEGIN
    count_ := (SELECT count (*) FROM pasajeros);
    RAISE NOTICE 'number of registers %', count_;
    INSERT INTO cont_pasajeros (total,tiempo) VALUES (count_, now());
    RETURN NULL;  -- added to fix the error
END
$BODY$;

ALTER FUNCTION public.my_func()
    OWNER TO postgres;

Upvotes: 1

Related Questions