Error Message: Control reached end of procedure without RETURN

Question

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

Laurenz Albe · Accepted Answer · 2021-04-09 17:07:52Z

1

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

I drop the function:
```
DROP FUNCTION public.my_func();
```

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;

edited Apr 9, 2021 at 17:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Apr 9, 2021 at 16:36

COVID-19

112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error Message: Control reached end of procedure without RETURN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related