Re: Calling function (table_name, schema_name) within event trigger - Mailing list pgsql-general

From Andreas Kretschmer
Subject Re: Calling function (table_name, schema_name) within event trigger
Date
Msg-id 20151228112316.GA4875@tux
Whole thread Raw
In response to Re: Calling function (table_name, schema_name) within event trigger  (Susan Hurst <susan.hurst@brookhurstdata.com>)
List pgsql-general
Susan Hurst <susan.hurst@brookhurstdata.com> wrote:

> hmmmm...well, Tom, at least you saved me a lot of frustration with
> trying to get this to work :-)  For the time being, I'll just follow up
> DDL activity with a procedure that compares diffs between
> information_schema and the history tables.  If and when pl/pgsql offers
> the capture of DDL activity on the fly, I can just move my code to an
> event trigger.

This works in 9.5:

CREATE FUNCTION test_event_trigger_for_create()
        RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        RAISE NOTICE '% created object: %',
                     tg_tag,
                     obj.object_identity;
    END LOOP;
END
$$;
CREATE EVENT TRIGGER test_event_trigger_for_create
   ON ddl_command_end
   EXECUTE PROCEDURE test_event_trigger_for_create();


Demo:

test=*# create table foo (i int);
NOTICE:  CREATE TABLE created object: public.foo
CREATE TABLE
test=*#




Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


pgsql-general by date:

Previous
From: Hiroyuki Sato
Date:
Subject: Re: grep -f keyword data query
Next
From: Francisco Olarte
Date:
Subject: Re: Options for complex materialized views sharing most of the same logic?