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
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 |