rails psql with structure dump uses PROCEDURE over FUNCTION-CodePudding

Every time I dump my structure.sql on a rails app, I get PROCEDURE over FUNCTION. FUNCTION is our default and I have to commit the file in parts which is annoying and sometimes I miss lines which is even worse, as it is a rather big structure.sql file.

git diff example:

-CREATE TRIGGER cache_comments_count AFTER INSERT OR DELETE OR UPDATE ON public.comments FOR EACH ROW EXECUTE PROCEDURE public.update_comments_counter();
 CREATE TRIGGER cache_comments_count AFTER INSERT OR DELETE OR UPDATE ON public.comments FOR EACH ROW EXECUTE FUNCTION public.update_comments_counter();

I'm sure there is a postgresql setting for this somewhere, but I can't find it.

CodePudding user response：

Whether you use Function or Procedure you get exactly the same. The documentation shows

CREATE [ CONSTRAINT ] TRIGGER name... 
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

This means you can use either term FUNCTION or PROCEDURE but either way function_name is always called. See demo. For demo I have separate triggers for insert and update. Insert using execute procedure and update using execute function. This cannot be changed in Postgres it would have to be Rails setting. NOTE: Prior to v11 Postgres only allowed execute procedure even though you had to create a trigger function that was called.

CodePudding user response：

The function pg_get_triggerdef() changed between Postgres 11 and 12 when Postgres introduced real procedures. Since Postgres 12 it always returns a syntax that uses EXECUTE FUNCTION as in reality it is a function that is called when the trigger fires, not a procedure.

So this code:

create table t1 (id int);

create function trg_func()
returns trigger
as
$$
begin
return new;
end;
$$
language plpgsql;

create trigger test_trigger
  before insert or update 
  on t1
  for each row
  execute procedure trg_func();

select pg_get_triggerdef(oid)
from pg_trigger 
where tgname = 'test_trigger';

returns the following in Postgres 11 and earlier:

CREATE TRIGGER test_trigger BEFORE INSERT OR UPDATE ON public.t1 FOR EACH ROW EXECUTE PROCEDURE trg_func()

and the following in Postgres 12 and later:

CREATE TRIGGER test_trigger BEFORE INSERT OR UPDATE ON public.t1 FOR EACH ROW EXECUTE FUNCTION trg_func()

I guess Rails uses pg_get_triggerdef() to obtain the trigger source. So there is nothing you can do. If you want a consistent result, you should use the same Postgres version everywhere.