Create DDL trigger to catch which column was altered

From: Łukasz Jarych <jaryszek(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org>
Subject: Create DDL trigger to catch which column was altered
Date: 2018-07-09 11:38:16
Message-ID: CAGv31oc68XtuP=MR47S-vXVxBrZ-4AEw9vKAmoKU1TxVOcKZEg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi Guys,

i am using sqls like below to track ddl changes:

CREATE TABLE track_ddl
> (
> event text,
> command text,
> ddl_time timestamptz,
> usr text
> );
> CREATE OR REPLACE FUNCTION track_ddl_function()
> RETURNS event_trigger
> AS
> $$
> BEGIN
> INSERT INTO track_ddl values(tg_tag, tg_event, now(), session_user);
> RAISE NOTICE 'DDL logged';
> END
> $$ LANGUAGE plpgsql SECURITY DEFINER;
>

> CREATE EVENT TRIGGER track_ddl_event ON ddl_command_start
> WHEN TAG IN ('CREATE TABLE', 'DROP TABLE', 'ALTER TABLE')
> EXECUTE PROCEDURE track_ddl_function();
> CREATE TABLE event_check(i int);
> SELECT * FROM track_ddl;

And and drop table is ok. But when i am altering i would like to know new
vales and old values like when i am catching DML changes:

CREATE OR REPLACE FUNCTION change_trigger() RETURNS trigger AS $$
>
>
>> BEGIN
>
>
>> IF TG_OP = 'INSERT'
>
>
>> THEN
>
>
>> INSERT INTO logging.t_history (tabname,
>> schemaname, operation, who, new_val)
>
>
>> VALUES (TG_RELNAME, TG_TABLE_SCHEMA,
>> TG_OP, current_user, row_to_json(NEW));
>
>
>> RETURN NEW;
>
>
>> ELSIF TG_OP = 'UPDATE'
>
>
>> THEN
>
>
>> INSERT INTO logging.t_history (tabname,
>> schemaname, operation, who, new_val, old_val)
>
>
>> VALUES (TG_RELNAME, TG_TABLE_SCHEMA,
>> TG_OP, current_user,
>
>
>> row_to_json(NEW),
>> row_to_json(OLD));
>
>
>> RETURN NEW;
>
>
>> ELSIF TG_OP = 'DELETE'
>
>
>> THEN
>
>
>> INSERT INTO logging.t_history (tabname,
>> schemaname, operation, who, old_val)
>
>
>> VALUES (TG_RELNAME, TG_TABLE_SCHEMA,
>> TG_OP, current_user, row_to_json(OLD));
>
>
>> RETURN OLD;
>
>
>> END IF;
>
>
>> END;
>
>
>> $$ LANGUAGE 'plpgsql' SECURITY DEFINER;
>
>
It is possible?
Or write function which will tell me all new values in new columns?

I was trying to change sqls like here:

CREATE TABLE track_ddl
> (
> event text,
> command text,
> ddl_time timestamptz,
> usr json
> );
> CREATE OR REPLACE FUNCTION track_ddl_function()
> RETURNS event_trigger
> AS
> $$
> BEGIN
> INSERT INTO track_ddl values(tg_tag, tg_event, now(), row_to_json(NEW));
> RAISE NOTICE 'DDL logged';
> END
> $$ LANGUAGE plpgsql SECURITY DEFINER;

but this is not working.

Please help,
Jacek

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Fetter 2018-07-09 13:01:05 Re: How to set array element to null value
Previous Message Łukasz Jarych 2018-07-09 11:03:53 Create event triger