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 $$
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
It is no possible?
Jacek
pon., 9 lip 2018 o 13:38 Łukasz Jarych <jaryszek@gmail.com> napisał(a):
Show quoted text
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
On 07/10/2018 01:56 AM, Łukasz Jarych wrote:
It is no possible?
AFAIK it is not possible, per:
/messages/by-id/3385.1518828768@sss.pgh.pa.us
Jacek
--
Adrian Klaver
adrian.klaver@aklaver.com