AS cardinal_number)AS action_order,

CAST(

CASE WHEN pg_has_role(c.relowner,'USAGE')

THEN (regexp_match(pg_get_triggerdef(t.oid), E'.{35,} WHEN\\((.+)\\) EXECUTEPROCEDURE'))[1]

THEN (regexp_match(pg_get_triggerdef(t.oid), E'.{35,} WHEN\\((.+)\\) EXECUTEFUNCTION'))[1]

ELSEnull END

AS character_data)AS action_condition,

CAST(

substring(pg_get_triggerdef(t.oid)from

position('EXECUTEPROCEDURE'insubstring(pg_get_triggerdef(t.oid)from48))+47)

position('EXECUTEFUNCTION'insubstring(pg_get_triggerdef(t.oid)from48))+47)

AS character_data)AS action_statement,

CAST(

appendStringInfoString(&buf,") ");

}

appendStringInfo(&buf,"EXECUTEPROCEDURE %s(",

appendStringInfo(&buf,"EXECUTEFUNCTION %s(",

generate_function_name(trigrec->tgfoid,0,

NIL,argtypes,

false,NULL,EXPR_KIND_NONE));

appendPQExpBufferStr(query, " FOR EACH STATEMENT\n ");

/* regproc output is already sufficiently quoted */

appendPQExpBuffer(query, "EXECUTEPROCEDURE %s(",

appendPQExpBuffer(query, "EXECUTEFUNCTION %s(",

tginfo->tgfname);

tgargs = (char *) PQunescapeBytea((unsigned char *) tginfo->tgargs,

appendPQExpBufferChar(query, ')');

}

appendPQExpBufferStr(query, "\n EXECUTEPROCEDURE ");

appendPQExpBufferStr(query, "\n EXECUTEFUNCTION ");

appendPQExpBufferStr(query, evtinfo->evtfname);

appendPQExpBufferStr(query, "();\n");

(8 rows)

SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';

pg_get_triggerdef

--------------------------------------------------------------------------------------------------------------------------------------------

CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.a <> new.a) EXECUTEPROCEDURE trigger_func('modified_a')

pg_get_triggerdef

-------------------------------------------------------------------------------------------------------------------------------------------

CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.a <> new.a) EXECUTEFUNCTION trigger_func('modified_a')

(1 row)

SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';

pg_get_triggerdef

-----------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TRIGGER modified_a BEFORE UPDATE OF a ON public.main_table FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTEPROCEDURE trigger_func('modified_a')

pg_get_triggerdef

----------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TRIGGER modified_a BEFORE UPDATE OF a ON public.main_table FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTEFUNCTION trigger_func('modified_a')

(1 row)

SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any';

pg_get_triggerdef

--------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTEPROCEDURE trigger_func('modified_any')

pg_get_triggerdef

-------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTEFUNCTION trigger_func('modified_any')

(1 row)

-- Test RENAME TRIGGER

CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON main_table

FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_b_stmt');

SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';

pg_get_triggerdef

--------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table FOR EACH ROW EXECUTEPROCEDURE trigger_func('after_upd_a_b_row')

pg_get_triggerdef

-------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table FOR EACH ROW EXECUTEFUNCTION trigger_func('after_upd_a_b_row')

(1 row)

UPDATE main_table SET a = 50;

a | integer | | |

b | integer | | |

Triggers:

after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTEPROCEDURE view_trigger('after_view_del_stmt')

after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTEPROCEDURE view_trigger('after_view_ins_stmt')

after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTEPROCEDURE view_trigger('after_view_upd_stmt')

before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTEPROCEDURE view_trigger('before_view_del_stmt')

before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTEPROCEDURE view_trigger('before_view_ins_stmt')

before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTEPROCEDURE view_trigger('before_view_upd_stmt')

instead_of_delete_trig INSTEAD OF DELETE ON main_view FOR EACH ROW EXECUTEPROCEDURE view_trigger('instead_of_del')

instead_of_insert_trig INSTEAD OF INSERT ON main_view FOR EACH ROW EXECUTEPROCEDURE view_trigger('instead_of_ins')

instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTEPROCEDURE view_trigger('instead_of_upd')

after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTEFUNCTION view_trigger('after_view_del_stmt')

after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTEFUNCTION view_trigger('after_view_ins_stmt')

after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTEFUNCTION view_trigger('after_view_upd_stmt')

before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTEFUNCTION view_trigger('before_view_del_stmt')

before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTEFUNCTION view_trigger('before_view_ins_stmt')

before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTEFUNCTION view_trigger('before_view_upd_stmt')

instead_of_delete_trig INSTEAD OF DELETE ON main_view FOR EACH ROW EXECUTEFUNCTION view_trigger('instead_of_del')

instead_of_insert_trig INSTEAD OF INSERT ON main_view FOR EACH ROW EXECUTEFUNCTION view_trigger('instead_of_ins')

instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTEFUNCTION view_trigger('instead_of_upd')

-- Test dropping view triggers

DROP TRIGGER instead_of_insert_trig ON main_view;

main_table.b

FROM main_table;

Triggers:

after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTEPROCEDURE view_trigger('after_view_del_stmt')

after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTEPROCEDURE view_trigger('after_view_ins_stmt')

after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTEPROCEDURE view_trigger('after_view_upd_stmt')

before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTEPROCEDURE view_trigger('before_view_del_stmt')

before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTEPROCEDURE view_trigger('before_view_ins_stmt')

before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTEPROCEDURE view_trigger('before_view_upd_stmt')

instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTEPROCEDURE view_trigger('instead_of_upd')

after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTEFUNCTION view_trigger('after_view_del_stmt')

after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTEFUNCTION view_trigger('after_view_ins_stmt')

after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTEFUNCTION view_trigger('after_view_upd_stmt')

before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTEFUNCTION view_trigger('before_view_del_stmt')

before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTEFUNCTION view_trigger('before_view_ins_stmt')

before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTEFUNCTION view_trigger('before_view_upd_stmt')

instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTEFUNCTION view_trigger('instead_of_upd')

DROP VIEW main_view;

--