我有一个接受表名数组的函数（ 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）;
我现在在运行选择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.
使用 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.
rextester/GUAJ1339
更多推荐
具有当前表名称的Postgresql动态函数
发布评论