当前位置：文江博客话题详情

Postgres：在模式中有一列的强制

发布于 2025-01-23 13:01:48 字数 58 浏览 0 评论 0原文

是否可以拦截创建表语句，并确保存在或返回异常？

有人有例子吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

吃→可爱长大的 2025-01-30 13:01:48

创建表格之后，创建表事件触发。您可以获取创建表的OID，并在系统目录pg_attribute中搜索其列。示例：

create or replace function create_table_event() 
returns event_trigger language plpgsql as $
declare 
    r record;
begin
    for r in 
        select * 
        from pg_event_trigger_ddl_commands() 
        where command_tag = 'CREATE TABLE'
    loop
        if not exists(
            select from pg_attribute
            where attrelid = r.objid
            and attname = 'column1') 
        then
            raise exception 'cannot create table %; table must have "column1" column', r.object_identity;
        end if;
    end loop;
end
$;

create event trigger create_table_event
    on ddl_command_end when tag in ('CREATE TABLE')
    execute procedure create_table_event();

在文档中阅读更多信息：

CREATE TABLE event trigger fires after a table is created. You can get the OID of the created table and search for its columns in the system catalog pg_attribute. Example:

create or replace function create_table_event() 
returns event_trigger language plpgsql as $
declare 
    r record;
begin
    for r in 
        select * 
        from pg_event_trigger_ddl_commands() 
        where command_tag = 'CREATE TABLE'
    loop
        if not exists(
            select from pg_attribute
            where attrelid = r.objid
            and attname = 'column1') 
        then
            raise exception 'cannot create table %; table must have "column1" column', r.object_identity;
        end if;
    end loop;
end
$;

create event trigger create_table_event
    on ddl_command_end when tag in ('CREATE TABLE')
    execute procedure create_table_event();