在PostgreSQL会话结束时删除触发器/函数？ [英] Drop trigger/function at end of session in PostgreSQL?

问题描述

是否可以创建在会话结束时自动删除的触发器或函数？还是说当我断开连接时运行此SQL？

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"?

推荐答案

在相当新的Postgres版本中，您可以在<$中创建函数c $ c> pg_temp 模式：

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();

因为此触发函数始终返回 NULL 和在[$ event] 之前是，它应该对该表进行任何写操作都将被忽略。而且确实是：

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.

这篇关于在PostgreSQL会话结束时删除触发器/函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！