postgresql:如何用函数修改pg_catalog？

Question

您好，我想创建一个函数来在 pg_catalog 中创建一个表，例如:

CREATE OR REPLACE FUNCTION foo() RETURNS void AS $$
DECLARE

BEGIN
EXECUTE 'CREATE TABLE pg_catalog.tab(a integer)';   
        RETURN;

END;
$$ LANGUAGE plpgsql;

错误:创建“pg_catalog.tab”的权限被拒绝
详细信息:目前不允许修改系统目录。

我编辑我的问题以添加更多详细信息:

我想创建一个在删除表时启动的函数。我认为唯一的方法是在表 pg_catalog.tables 上创建触发器:

CREATE OR REPLACE FUNCTION a() RETURNS void AS $$
DECLARE
BEGIN

  EXECUTE 'CREATE TRIGGER "tt_drop" BEFORE DELETE ON pg_catalog.pg_tables EXECUTE PROCEDURE public.b()';
  RETURN;

END;
$$ LANGUAGE plpgsql;

在函数 b 中，我想在删除表之前对其进行一些操作...

Answer 1

出于实际和主要的安全原因，这是被禁止的 - pg_catalog 始终位于搜索路径的第一位，没有人可以更改它，因为 PostgreSQL 团队希望 pg_catalog 中的函数不会被自定义函数覆盖。

存在一些非常丑陋的技巧，但我不建议使用它。

稍微不那么难看，但仍然非常难看的是将 Postgres 切换到单一模式 - 任何安全机制都已关闭 - 但数据库损坏的风险很高。因此，只有完全了解 Postgres 内部结构的人才能使用它。

回复更新的问题:

您不能在系统表上创建触发器。但是有event triggers这可能就是您想要的(PostgreSQL 9.3 支持)。

CREATE FUNCTION test_event_trigger_for_drops()
        RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
    LOOP
        RAISE NOTICE '% dropped object: % %.% %',
                     tg_tag,
                     obj.object_type,
                     obj.schema_name,
                     obj.object_name,
                     obj.object_identity;
    END LOOP;
END
$$;

CREATE EVENT TRIGGER test_event_trigger_for_drops
   ON sql_drop
   EXECUTE PROCEDURE test_event_trigger_for_drops();

postgres=# DROP TABLE obce CASCADE;
NOTICE:  drop cascades to materialized view obce_stredocesky_kraj
NOTICE:  DROP TABLE dropped object: table public.obce public.obce
NOTICE:  DROP TABLE dropped object: index public.obce_okres_id_idx public.obce_okres_id_idx
NOTICE:  DROP TABLE dropped object: table constraint public.<NULL> obce_okres_id_fk on public.obce
NOTICE:  DROP TABLE dropped object: trigger <NULL>.<NULL>  "RI_ConstraintTrigger_a_46471" on public.okresy
NOTICE:  DROP TABLE dropped object: trigger <NULL>.<NULL> "RI_ConstraintTrigger_a_46472" on public.okresy
NOTICE:  DROP TABLE dropped object: trigger <NULL>.<NULL> "RI_ConstraintTrigger_c_46473" on public.obce
NOTICE:  DROP TABLE dropped object: trigger <NULL>.<NULL> "RI_ConstraintTrigger_c_46474" on public.obce
NOTICE:  DROP TABLE dropped object: sequence public.obce_id_seq public.obce_id_seq
NOTICE:  DROP TABLE dropped object: type public.obce_id_seq public.obce_id_seq
NOTICE:  DROP TABLE dropped object: default value <NULL>.<NULL> for public.obce.id
NOTICE:  DROP TABLE dropped object: table constraint public.<NULL> _obce_pkey on public.obce
NOTICE:  DROP TABLE dropped object: index public._obce_pkey public._obce_pkey
NOTICE:  DROP TABLE dropped object: materialized view public.obce_stredocesky_kraj public.obce_stredocesky_kraj
NOTICE:  DROP TABLE dropped object: index public.obce_stredocesky_kraj_nazev_idx public.obce_stredocesky_kraj_nazev_idx
NOTICE:  DROP TABLE dropped object: type public.obce_stredocesky_kraj public.obce_stredocesky_kraj
NOTICE:  DROP TABLE dropped object: type public._obce_stredocesky_kraj public.obce_stredocesky_kraj[]
NOTICE:  DROP TABLE dropped object: rule <NULL>.<NULL> "_RETURN" on public.obce_stredocesky_kraj
NOTICE:  DROP TABLE dropped object: type public.obce public.obce
NOTICE:  DROP TABLE dropped object: type public._obce public.obce[]
DROP TABLE   

看

• http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-event-triggers/
• http://www.depesz.com/2012/07/29/waiting-for-9-3-event-triggers/
• http://www.jcdba.com/exploring-postgresql-9-3-event-triggers/