Re: Create DDL trigger to catch which column was altered

[Łukasz Jarych <jaryszek@xxxxxxxxx>]

It is no possible?

Jacek

pon., 9 lip 2018 o 13:38 Łukasz Jarych <jaryszek@xxxxxxxxx> 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