Re: event triggers in 9.3.4

On Thu, Jul 24, 2014 at 2:22 AM, Vasudevan, Ramya <
ramya(dot)vasudevan(at)classmates(dot)com> wrote:

> CREATE TABLE log_ddl_info(ddl_tag text, ddl_event text, ddl_time
> timestamp);
>
> CREATE OR REPLACE FUNCTION log_ddl_execution()
>
> RETURNS event_trigger AS $$
>
> DECLARE
>
> insertquery TEXT;
>
> BEGIN
>
> insertquery := 'INSERT INTO log_ddl_info VALUES (''' || tg_tag ||''', '''
> || tg_event || ''', statement_timestamp())';
>
> EXECUTE insertquery;
>

Why are you using dynamic query and not just run insert normally? And even
for dynamic query it shows basically a worst possible way to do it, that is
prone to sql injection. Of course the problem is unlikely now given that
the values come from pg itself, and have pretty well defined values, but
why do it unsafely even in such simple case?!

RAISE NOTICE 'Recorded execution of command % with event %', tg_tag,
> tg_event;
>
> END;
>
> $$ LANGUAGE plpgsql;
>
>
>
> CREATE EVENT TRIGGER log_ddl_info_start ON ddl_command_start EXECUTE
> PROCEDURE log_ddl_execution();
>
> CREATE EVENT TRIGGER log_ddl_info_end ON ddl_command_end EXECUTE
> PROCEDURE log_ddl_execution();
>
> Is there a way to log the object name (or the oid) in the function?
>
>

You could compare list of tables before (_start) and after (_end) the ddl.
Doing it in plpgsql will be tricky, but if you'd use some other language -
like plperl - it's relatively simple:
http://www.depesz.com/2013/12/18/waiting-for-9-4-plperl-add-event-trigger-support/

depesz