Postgresql dynamic function with current table name

Question

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'])

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)||';
                                           ^

Here's the function:

    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:

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 format($string$INSERT INTO audit.%1$s VALUES (audit_row.*);$string$, TG_TABLE_NAME::text);

I am now getting this error when I run select audit.create_audit_table(ARRAY['organization'])

ERROR:

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

Here's the updated function:

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;

remove quote_ident. quote_ident(TG_TABLE_NAME::TEXT) will apply necessary actions to correcty quote the argument as relation name. So you basically tell TG_TABLE_NAME is not variable, but a table name — Vao Tsun
– Vao Tsun, Commented Mar 30, 2017 at 7:31

pozs · Accepted Answer · 2017-04-03 09:49:16Z

2

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

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

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.

Notes:

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.

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;

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;

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

http://rextester.com/GUAJ1339

edited Apr 3, 2017 at 9:49

answered Mar 30, 2017 at 8:07

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

iambdot Over a year ago

when you say "Inside the trigger function, you cannot use a %ROWTYPE variable" are you talking about this line audit_row audit.' || quote_ident(TG_TABLE_NAME::TEXT)||';

pozs Over a year ago

@iambdot yes. you could use DECLARE audit_row RECORD there.

pozs Over a year ago

@lambdot in EXECUTE, you can't access variables, which were declated in the PL/pgSQL context. You'll need something like EXECUTE format('INSERT INTO audit.%1$s VALUES ($1::%1$s.*)', TG_TABLE_NAME::text) USING audit_row (the cast ::%1$s is required for PostgreSQL to understand what kind on RECORD is audit_row exactly).

iambdot Over a year ago

thanks for that.i made the change and i still get this error when i do an update to the table

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

. it looks like it's failing because of statements like audit_row.row_data = OR even audit_row.changed_fields =. is there a way to assign fields to the RECORD type? if not, should I just create a temp table and insert the data in there and then select the values from it for the main INSERT?

pozs Over a year ago

@iambdot I just updated my answer with a working example to insert (and manipulate) the RECORD type.

|

Vao Tsun · Accepted Answer · 2017-03-30 07:34:10Z

1

quote_ident(TG_TABLE_NAME::TEXT) will apply necessary actions to correcty quote the argument as relation name.

I would recommend using execute format('statement') instead of concatinations, eg:

t=# do $$ begin raise info '%',format('I am %I, now is %L',current_user,now()); end;$$;
INFO:  I am postgres, now is '2017-03-30 07:33:53.579476+00'
DO

Instead of:

t=# do $$ begin raise info '%','I am '||quote_ident(current_user)||', now is '||quote_ident(now()::text); end;$$;
INFO:  I am postgres, now is "2017-03-30 07:36:20.495887+00"
DO

answered Mar 30, 2017 at 7:34

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Postgresql dynamic function with current table name

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related