Can not make function in postgresql

Question

I'm not new to postgresql, but also I'm not exactly a specialist.

I have executed this sql to create a function in postgresql :

CREATE OR REPLACE FUNCTION public.trg_corrigeer_geometrie()
  RETURNS trigger AS
$BODY$    
    BEGIN
        IF NOT (st_isvalid(NEW.geometry)) THEN
            NEW.geometry := st_multi(public.cleangeometry(NEW.geometry));
        END IF; 
        RETURN NEW;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.trg_corrigeer_geometrie()
  OWNER TO postgres;

It returnes "Query returned sucessfully with ... "

When I try to check if it exists:

select * from pg_trigger where tgname = 'trg_corrigeer_geometrie'

It gives me an empty table. I don't really get why? The function public.cleangeometry does exist, it doesn't give me an error, so none of that can be the problem. I'm really a bit baffled by this.

Answer 1

You have succesfully created a function. Now you would need to bind it to an actual trigger:

create trigger trg_corriger_geometrie
before insert -- or "update" ?
on mytable
for each row
    execute procedure func_corriger_geometrie()
;

I renamed the function from trg_corriger_geometrie() to func_corriger_geometrie() so the former can be used as the trigger name.

Demo on DB Fiddle:

oid    | tgrelid | tgname                 | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgoldtable | tgnewtable
:----- | :------ | :--------------------- | :----- | -----: | :-------- | :----------- | :------------ | :------------ | :----------- | :----------- | :------------- | ------: | :----- | :----- | :----- | :--------- | :---------
367288 | 367284  | trg_corriger_geometrie | 367287 |      7 | O         | f            | 0             | 0             | 0            | f            | f              |       0 |        | \x     | null   | null       | null