Re: Create DDL trigger to catch which column was altered

It is no possible?

Jacek

pon., 9 lip 2018 o 13:38 Łukasz Jarych <jaryszek(at)gmail(dot)com> napisał(a):

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