Обсуждение: Create DDL trigger to catch which column was altered
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 $$BEGINIF TG_OP = 'INSERT'THENINSERT 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'THENINSERT 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'THENINSERT 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
It is no possible?
Jacek
pon., 9 lip 2018 o 13:38 Łukasz Jarych <jaryszek@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 $$BEGINIF TG_OP = 'INSERT'THENINSERT 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'THENINSERT 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'THENINSERT 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
On 07/10/2018 01:56 AM, Łukasz Jarych wrote: > It is no possible? AFAIK it is not possible, per: https://www.postgresql.org/message-id/3385.1518828768%40sss.pgh.pa.us > > Jacek > -- Adrian Klaver adrian.klaver@aklaver.com