postgresql 删除触发器_在PostgreSQL会话结束时删除触发器/函数？

Is it possible to create a trigger or function that gets automatically dropped at the end of the session? Or otherwise say "run this SQL when I disconnect"?

解决方案

In reasonably recent Postgres versions you can create a function in pg_temp schema:

create function pg_temp.get_true() returns boolean language sql as $$ select true; $$;

select pg_temp.get_true();

This is the schema in which temporary tables are created. All its contents, including your function, will be deleted on end of session.

You can also create triggers using temporary functions on tables. I've just tested this and it works as expected:

create function pg_temp.ignore_writes() returns trigger language plpgsql as $$

begin

return NULL;

end;

$$;

create table test (id int);

create trigger test_ignore_writes

before insert, update, delete on test

for each row execute procedure pg_temp.ignore_writes();

Because this trigger function always returns NULL and is before [event] it should make any writes to this table to be ignored. And indeed:

insert into test values(1);

select count(*) from test;

count

-------

0

But after logout and login this function and the trigger would not be present anymore, so writes would work:

insert into test values(1);

select count(*) from test;

count

-------

1

But you should be aware that this is somewhat hackish — not often used and might not be very thoroughly tested.