如何为创建表创建事件触发器或选择进入 [英] how to create event trigger for create table or select into

问题描述

我想要创建事件触发器以创建表或选择进入， 例如: 当创建表xxxx时，表名必须以'temp'变大

i want create event trigger for create table or select into, eg: when create table xxxx must table name bigen with 'temp'

我的代码

    CREATE OR REPLACE FUNCTION create_table_func()
    RETURNS event_trigger
    AS
    $$
    DECLARE
          V_TABLE name := TG_TABLE_NAME;

BEGIN

    if V_TABLE !~ '^temp'

    then

      RAISE EXCEPTION 'must bigen with temp';

    end if;

END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE EVENT TRIGGER create_table_1 ON ddl_command_start

WHEN TAG IN ('SELECT INTO')

EXECUTE PROCEDURE create_table_func();

但是执行时 从test_bak选择*进入test11

but when execute select * into test11 from test_bak

[Err]错误:列"tg_table_name"不存在

[Err] ERROR: column "tg_table_name" does not exist

推荐答案

特殊变量TG_TABLE_NAME仅在常规触发器中受支持，在事件触发器中不受支持(并不总是有关联的表！).

The special variable TG_TABLE_NAME is only supported in normal triggers, not in event triggers (there is not always an associated table!).

文档中有函数列表可以在事件触发器中返回上下文信息.

The documentation has a list of functions that can return context information in an event trigger.

您可以使用pg_event_trigger_ddl_commands()来获取所需的信息，但这仅在ddl_command_end事件触发器中起作用.那应该为您工作；我看不出触发器不应该在语句末尾运行的原因.

You could use pg_event_trigger_ddl_commands() to get the information you need, but that only works in ddl_command_end event triggers. That should work for you; I don't see a reason why the trigger should not run at the end of the statement.

这篇关于如何为创建表创建事件触发器或选择进入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！