It is no possible? Jacek
pon., 9 lip 2018 o 13:38 Łukasz Jarych <jarys...@gmail.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 > >