Question 113700 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – rails + psql with structure dump uses PROCEDURE over FUNCTION

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    The column action_statement in the view information_schema.triggers also reflects the change in naming.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search