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.

GMB · Accepted Answer · 2020-03-24 22:32:19Z

3

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

answered Mar 24, 2020 at 22:32

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

BartM Over a year ago

Thank you so much. I was really stuck with this!

Collectives™ on Stack Overflow

Can not make function in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related