Create DDL trigger to catch which column was altered

Started by Łukasz Jarychalmost 8 years ago3 messagesgeneral
Jump to latest
#1Łukasz Jarych
jaryszek@gmail.com

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

#2Łukasz Jarych
jaryszek@gmail.com
In reply to: Łukasz Jarych (#1)
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@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

#3Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Łukasz Jarych (#2)
Re: Create DDL trigger to catch which column was altered

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