PL/pgSQL 动态触发器

从 PL/pgSQL 触发器中访问 NEW 中的字段

代码片段

PL/pgSQL 动态触发器

适用于 PostgreSQL

8.3

用

PL/pgSQL

依赖于

无

EXECUTE 'SELECT (' ||
         quote_literal(NEW) || '::' || TG_RELID::regclass ||
         ').' || quote_ident(columnname)
         INTO var;

自 8.4 版起有效

CREATE OR REPLACE FUNCTION dynamic_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    ri RECORD;
    t TEXT;
BEGIN
    RAISE NOTICE E'\n    Operation: %\n    Schema: %\n    Table: %',
        TG_OP,
        TG_TABLE_SCHEMA,
        TG_TABLE_NAME;
    FOR ri IN
        SELECT ordinal_position, column_name, data_type
        FROM information_schema.columns
        WHERE
            table_schema = TG_TABLE_SCHEMA
        AND table_name = TG_TABLE_NAME
        ORDER BY ordinal_position
    LOOP
        EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO t USING NEW;
        RAISE NOTICE E'Column\n    number: %\n    name: %\n    type: %\n    value: %.',
            ri.ordinal_position,
            ri.column_name,
            ri.data_type,
            t;
    END LOOP;
    RETURN NEW;
END;
$$;