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
