具有当前表名称的Postgresql动态函数

问题描述

我有一个接受表名数组的函数（ audit.create_audit_table（））。它创建单个函数 audit.if_modified_func（），然后遍历每个表名，创建一个审计表，并将触发器应用于主表。该函数可以编译并且不会出错。当我运行函数

I have a function (audit.create_audit_table()) that accepts an array of table names. It creates a single function audit.if_modified_func() and then loops through each table name and creates an audit table and applies a trigger to the main table. The function compiles and is created with no errors. When I run the function

select audit.create_audit_table(ARRAY['organization'])

我一直收到以下错误，我不确定为什么，因为我认为 TG_TABLE_NAME 是一个自动变量，它将使我能够访问正在执行 audit.if_modified_func（）

I keep getting the following error and I am not sure why because I thought that TG_TABLE_NAME is a automatic variable which will give me access to the current table that is executing audit.if_modified_func()

ERROR ：

ERROR:  column "tg_table_name" does not exist
LINE 3:   audit_row audit.' || quote_ident(TG_TABLE_NAME::TEXT)||';
                                           ^

这里是函数：

    CREATE OR REPLACE FUNCTION audit.create_audit_table(table_names character varying[])
RETURNS character varying AS
$BODY$
    DECLARE
    table_name varchar;
    i int;
BEGIN
    EXECUTE 'CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS TRIGGER AS $$
    DECLARE     
        audit_row audit.' || quote_ident(TG_TABLE_NAME::TEXT)||';
        include_values boolean;
        log_diffs boolean;
        h_old hstore;
        h_new hstore;
        excluded_cols text[] = ARRAY[]::text[];
    BEGIN
        IF TG_WHEN <> ''AFTER'' THEN
            RAISE EXCEPTION ''audit.if_modified_func() may only run as an AFTER trigger'';
        END IF;

        audit_row = ROW(
            nextval(''audit.'|| quote_ident(TG_TABLE_NAME::text) ||'_event_id_seq''), -- event_id
            TG_TABLE_SCHEMA::text,                        -- schema_name
            TG_TABLE_NAME::text,                          -- table_name
            TG_RELID,                                     -- relation OID for much quicker searches
            session_user::text,                           -- session_user_name
            current_timestamp,                            -- action_tstamp_tx
            statement_timestamp(),                        -- action_tstamp_stm
            clock_timestamp(),                            -- action_tstamp_clk
            txid_current(),                               -- transaction ID
            current_setting(''application_name''),          -- client application
            inet_client_addr(),                           -- client_addr
            inet_client_port(),                           -- client_port
            current_query(),                              -- top-level query or queries (if multistatement) from client
            substring(TG_OP,1,1),                         -- action
            NULL, NULL,                                   -- row_data, changed_fields
            ''f''                                           -- statement_only
            );

        IF NOT TG_ARGV[0]::boolean IS DISTINCT FROM ''f''::boolean THEN
            audit_row.client_query = NULL;
        END IF;

        IF TG_ARGV[1] IS NOT NULL THEN
            excluded_cols = TG_ARGV[1]::text[];
        END IF;

        IF (TG_OP = ''UPDATE'' AND TG_LEVEL = ''ROW'') THEN
            audit_row.row_data = hstore(OLD.*) - excluded_cols;
            audit_row.changed_fields =  (hstore(NEW.*) - audit_row.row_data) - excluded_cols;
            IF audit_row.changed_fields = hstore('''') THEN
                -- All changed fields are ignored. Skip this update.
                RETURN NULL;
            END IF;
        ELSIF (TG_OP = ''DELETE'' AND TG_LEVEL = ''ROW'') THEN
            audit_row.row_data = hstore(OLD.*) - excluded_cols;
        ELSIF (TG_OP = ''INSERT'' AND TG_LEVEL = ''ROW'') THEN
            audit_row.row_data = hstore(NEW.*) - excluded_cols;
        ELSIF (TG_LEVEL = ''STATEMENT'' AND TG_OP IN (''INSERT'',''UPDATE'',''DELETE'',''TRUNCATE'')) THEN
            audit_row.statement_only = ''t'';
        ELSE
            RAISE EXCEPTION ''[audit.if_modified_func] - Trigger func added as trigger for unhandled case: %%, %%'',TG_OP, TG_LEVEL;
            RETURN NULL;
        END IF;
        INSERT INTO audit.'|| quote_ident(TG_TABLE_NAME::TEXT) ||' VALUES (audit_row.*);
    RETURN null;
    END;
    $$
    LANGUAGE plpgsql;   
    ALTER FUNCTION audit.if_modified_func()
    OWNER TO postgres;';

    FOR i in 1..array_upper(table_names, 1) LOOP

        EXECUTE format('
        DROP TABLE IF EXISTS audit.%1$s;
        CREATE TABLE audit.%1$s (
        event_id bigserial primary key,
        schema_name text not null,
        table_name text not null,
        relid oid not null,
        session_user_name text,
        action_tstamp_tx TIMESTAMP WITH TIME ZONE NOT NULL,
        action_tstamp_stm TIMESTAMP WITH TIME ZONE NOT NULL,
        action_tstamp_clk TIMESTAMP WITH TIME ZONE NOT NULL,
        transaction_id bigint,
        application_name text,
        client_addr inet,
        client_port integer,
        client_query text,
        action TEXT NOT NULL CHECK (action IN (''I'',''D'',''U'', ''T'')),
        row_data hstore,
        changed_fields hstore,
        statement_only boolean not null
        );

        REVOKE ALL ON audit.%1$s FROM public;

        COMMENT ON TABLE audit.%1$s IS ''History of auditable actions on audited tables, from audit.if_modified_func()'';
        COMMENT ON COLUMN audit.%1$s.event_id IS ''Unique identifier for each auditable event'';
        COMMENT ON COLUMN audit.%1$s.schema_name IS ''Database schema audited table for this event is in'';
        COMMENT ON COLUMN audit.%1$s.table_name IS ''Non-schema-qualified table name of table event occured in'';
        COMMENT ON COLUMN audit.%1$s.relid IS ''Table OID. Changes with drop/create. Get with ''''tablename''''::regclass'';
        COMMENT ON COLUMN audit.%1$s.session_user_name IS ''Login / session user whose statement caused the audited event'';
        COMMENT ON COLUMN audit.%1$s.action_tstamp_tx IS ''Transaction start timestamp for tx in which audited event occurred'';
        COMMENT ON COLUMN audit.%1$s.action_tstamp_stm IS ''Statement start timestamp for tx in which audited event occurred'';
        COMMENT ON COLUMN audit.%1$s.action_tstamp_clk IS ''Wall clock time at which audited event''''s trigger call occurred'';
        COMMENT ON COLUMN audit.%1$s.transaction_id IS ''Identifier of transaction that made the change. May wrap, but unique paired with action_tstamp_tx.'';
        COMMENT ON COLUMN audit.%1$s.client_addr IS ''IP address of client that issued query. Null for unix domain socket.'';
        COMMENT ON COLUMN audit.%1$s.client_port IS ''Remote peer IP port address of client that issued query. Undefined for unix socket.'';
        COMMENT ON COLUMN audit.%1$s.client_query IS ''Top-level query that caused this auditable event. May be more than one statement.'';
        COMMENT ON COLUMN audit.%1$s.application_name IS ''Application name set when this audit event occurred. Can be changed in-session by client.'';
        COMMENT ON COLUMN audit.%1$s.action IS ''Action type; I = insert, D = delete, U = update, T = truncate'';
        COMMENT ON COLUMN audit.%1$s.row_data IS ''Record value. Null for statement-level trigger. For INSERT this is the new tuple. For DELETE and UPDATE it is the old tuple.'';
        COMMENT ON COLUMN audit.%1$s.changed_fields IS ''New values of fields changed by UPDATE. Null except for row-level UPDATE events.'';
        COMMENT ON COLUMN audit.%1$s.statement_only IS ''''''t'''' if audit event is from an FOR EACH STATEMENT trigger, ''''f'''' for FOR EACH ROW'';

        CREATE INDEX %1$s_relid_idx ON audit.%1$s(relid);
        CREATE INDEX %1$s_action_tstamp_tx_stm_idx ON audit.%1$s(action_tstamp_stm);
        CREATE INDEX %1$s_action_idx ON audit.%1$s(action);         
        ', table_names[i]);     

        EXECUTE format('
        DROP TRIGGER IF EXISTS audit_trigger_row ON %1$s;
        CREATE TRIGGER audit_trigger_row
        AFTER INSERT OR UPDATE OR DELETE
        ON public.%1$s
        FOR EACH ROW
        EXECUTE PROCEDURE audit.if_modified_func();', table_names[i]);

        EXECUTE format('
        DROP TRIGGER IF EXISTS audit_trigger_stm ON %1$s;
        CREATE TRIGGER audit_trigger_stm
        AFTER TRUNCATE
        ON public.%1$s
        FOR EACH STATEMENT
        EXECUTE PROCEDURE audit.if_modified_func();', table_names[i]);

    END LOOP;

RETURN 'SUCCESS';
END;
$BODY$
LANGUAGE plpgsql;
ALTER FUNCTION audit.create_audit_table(character varying[])
OWNER TO postgres;  

UPDATE 03/31：

好，所以我创建了 if_modified_func（）函数而不使用动态SQL，并且我将audit_row声明为 audit_row RECORD; 我不确定在插入值时需要强制转换部分。我也不确定这是否是插入的正确方法

Ok, so I created the if_modified_func() function without the dynamic sql and I declared the audit_row as audit_row RECORD; I am not sure about the part of "needing a cast upon inserting the values". I am also not sure if this is the correct way to do the insert

EXECUTE格式（$ string $ INSERT INTO audit。％1 $ s VALUES（audit_row。*）; $ string $，TG_TABLE_NAME :: text）;

我现在在运行<$时遇到此错误c $ c>选择audit.create_audit_table（ARRAY ['organization']）

错误：

ERROR:  record "audit_row" has no field "row_data"
CONTEXT:  PL/pgSQL function audit.if_modified_func() line 42 at assignment

以下是更新的函数：

CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS TRIGGER AS $$
        DECLARE     
            audit_row RECORD;            
            include_values boolean;
            log_diffs boolean;
            h_old hstore;
            h_new hstore;
            excluded_cols text[] = ARRAY[]::text[];
        BEGIN
            IF TG_WHEN <> 'AFTER' THEN
                RAISE EXCEPTION 'audit.if_modified_func() may only run as an AFTER trigger';
            END IF;

            audit_row = ROW(
                nextval(format('audit.%1$s_event_id_seq',TG_TABLE_NAME::text)), -- event_id
                TG_TABLE_SCHEMA::text,                        -- schema_name
                TG_TABLE_NAME::text,                          -- table_name
                TG_RELID,                                     -- relation OID for much quicker searches
                session_user::text,                           -- session_user_name
                current_timestamp,                            -- action_tstamp_tx
                statement_timestamp(),                        -- action_tstamp_stm
                clock_timestamp(),                            -- action_tstamp_clk
                txid_current(),                               -- transaction ID
                current_setting('application_name'),          -- client application
                inet_client_addr(),                           -- client_addr
                inet_client_port(),                           -- client_port
                current_query(),                              -- top-level query or queries (if multistatement) from client
                substring(TG_OP,1,1),                         -- action
                NULL, NULL,                                   -- row_data, changed_fields
                'f'                                           -- statement_only
                );

            IF NOT TG_ARGV[0]::boolean IS DISTINCT FROM 'f'::boolean THEN
                audit_row.client_query = NULL;
            END IF;

            IF TG_ARGV[1] IS NOT NULL THEN
                excluded_cols = TG_ARGV[1]::text[];
            END IF;

            IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN
                audit_row.row_data = hstore(OLD.*) - excluded_cols;
                audit_row.changed_fields =  (hstore(NEW.*) - audit_row.row_data) - excluded_cols;
                IF audit_row.changed_fields = hstore('') THEN
                    -- All changed fields are ignored. Skip this update.
                    RETURN NULL;
                END IF;
            ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN
                audit_row.row_data = hstore(OLD.*) - excluded_cols;
            ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN
                audit_row.row_data = hstore(NEW.*) - excluded_cols;
            ELSIF (TG_LEVEL = 'STATEMENT' AND TG_OP IN ('INSERT','UPDATE','DELETE','TRUNCATE')) THEN
                audit_row.statement_only = 't';
            ELSE
                RAISE EXCEPTION '[audit.if_modified_func] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL;
                RETURN NULL;
            END IF;
            EXECUTE format('INSERT INTO audit.%1$s VALUES (audit_row.*)', TG_TABLE_NAME::text);  

        RETURN null;
        END;
        $$
        LANGUAGE plpgsql;   
        ALTER FUNCTION audit.if_modified_func()
        OWNER TO postgres;

推荐答案

TG_TABLE_NAME 是一个特殊的触发器变量，仅在触发器函数内部可用。您的 create_audit_table（）不是触发函数。

TG_TABLE_NAME is a special, trigger variable, which is only available inside trigger functions. Your create_audit_table() is not a trigger function.

此外，您还在不断重新定义实际的触发函数（ if_modified_func（）），可无效任何先前创建的触发器。

Also, you constantly redefining your real trigger function (if_modified_func()), which "invalidates" any earlier created triggers.

创建您的触发器函数无动态SQL的魔力（动态SQL只需要在这些审计表中插入值）。然后，您可以将审计逻辑添加到具有以下内容的表中：

Create your trigger function without the dynamic SQL magic (dynamic SQL will only need to insert values to these audit tables). Then, you can add your audit logic to a table with:

CREATE TRIGGER audit_trigger_row
  AFTER INSERT OR UPDATE OR DELETE
  ON public.<your_table_name>
  FOR EACH ROW
  EXECUTE PROCEDURE <your_audit_trigger_function_name>();

您可以放置​​此对象（但只能放置此对象-如果存在的话，可以放置 ），以便更轻松地附加此审核逻辑。

You can put this (but only this -- maybe with drop if exists) inside a function, to allow attaching this audit logic more easily.

注释：

• 在触发函数中，您不能使用％ROWTYPE 变量（因为您不知道确切的表格（只有名称）。解决方案很简单：只需使用 RECORD 类型（尽管在插入值时需要强制转换）。


• Don'对于这么长的字符串，请不要使用单引号。请使用 $ your_keyword $< string_value> $ your_keyword $ 格式。可能使用 format（）函数，而不仅仅是连接值。您的代码将更具可读性。

• Inside the trigger function, you cannot use a %ROWTYPE variable (because you don't know the exact table. you only have its name). The solution is simple: just use the RECORD type instead (you will need a cast upon inserting the values though).
• Don't use single quotes for such long strings. Use the $your_keyword$<string_value>$your_keyword$ format instead. With possibly the format() function instead of just concatenating values. Your code will be much more readable.

编辑：利用您的 RECORD 变量，您应该：

Edit: to utilize your RECORD variable, you should either:

• 使用结构初始化它。

• Initialize it with a structure. You can do this in your case with f.ex.

SELECT nextval('audit.'|| quote_ident(TG_TABLE_NAME) || '_event_id_seq') AS event_id,
       TG_TABLE_SCHEMA                        AS schema_name,
       TG_TABLE_NAME                          AS table_name,
       TG_RELID                               AS relid,
       session_user                           AS session_user_name,
       current_timestamp                      AS action_tstamp_tx,
       statement_timestamp()                  AS action_tstamp_stm,
       clock_timestamp()                      AS action_tstamp_clk,
       txid_current()                         AS transaction_id,
       current_setting('application_name')    AS application_name,
       inet_client_addr()                     AS client_addr,
       inet_client_port()                     AS client_port,
       current_query()                        AS client_query,
       substring(TG_OP, 1, 1)                 AS action,
       NULL::hstore                           AS row_data,
       NULL::hstore                           AS changed_fields,
       FALSE                                  AS statement_only
INTO   audit_row;

使用 ROW（）构造函数。第一列的名称为 f1 ，第二列的名称为 f2 ，依此类推。

Use the predefined names of the ROW() constructor. The first column's name if f1, the second's is f2, etc.

audit_row.f15 = hstore(OLD.*) - excluded_cols;

选择上述方法之一后，您可以应该插入以下行：

After choosing one of the above methods, you should insert the row like:

EXECUTE format('INSERT INTO audit.%1$s VALUES (($1::text::audit.%1$s).*)', quote_ident(TG_TABLE_NAME)) USING audit_row;

注意：甚至强制转换为 text 是必需的，因为 EXECUTE 无法知道 audit_row 的实际结构。

Note: even the cast to text is required due to the fact that EXECUTE cannot know the actual structure of audit_row.

这篇关于具有当前表名称的Postgresql动态函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！