Re: Add CREATE support to event triggers - Mailing list pgsql-hackers

Pavel Stehule escribió:

> 2014/1/13 Alvaro Herrera <alvherre@2ndquadrant.com>

> > After playing with this for a while, I realized something that must have
> > seemed quite obvious to those paying attention: what this function is,
> > is just a glorified sprintf() for JSON.  So I propose we take our
> > existing format(text) and use it to model a new format(json) function,
> > which will be useful to the project at hand and be of more general
> > applicability.

> I am not against to this idea, although I don't see a strong benefit. .
> Just special function can be better - it has minimal relation to variadic
> "function" format - and nested mixed format can be messy

Yeah.  I eventually realized that I need some very specialized format
specifiers here; I invented on %{}T specifier, for instance, which is
used to format types.  So I think this is better confined to expansion
of SQL commands rather than a generic string formatter.

So here's a patch implementing the ideas expressed in this thread.
There are two new SQL functions:

pg_event_trigger_get_creation_commands()
Can be called in a ddl_command_end event, and returns a JSON blob for
each object created in that command.

pg_event_trigger_expand_command()
Takes such a JSON blob and turns it back into an executable command.

The usefulness of this combination is that the user can edit the JSON
between those two calls, say by adding new columns or changing or
removing schema specifications, tablespaces, and so on.

One interesting bit I had to add was format_type_detailed().  This
function returns a type specification in minute detail: schema, type
name, typemod, array are all returned separately.  This might seem
overkill, but if we want to let the user mess with column definitions, I
think it's necessary.

Some things are uglier than I would like -- one reason is I stayed away
from using the JSON things too directly.  There are at least two patches
in that area, and some helpers might appear that help this patch.
However, at the moment I am not sure whether the end result would be
better or worse, and I don't want to make this patch depend on some
other patch which might or might not end up being applied.  In any case,
the JSON stuff is pretty localized so it should be reasonably easy to
rip out and replace.  The first half of deparse_utility.c is concerned
with a simple-minded mechanism to accumulate an object hierarchy until
it's time to convert it to proper JSON.  Perhaps the new JSON stuff will
make it possible to rip that all out.

The JSON parsing is done in event_trigger.c.  This code should probably
live elsewhere, but I again hesitate to put it in json.c or jsonfuncs.c,
at least until some discussion about its general applicability takes
place.

The second half of deparse_utility.c is concerned with actually
processing the parse nodes to construct objects.  There are several
cases missing (at the moment, only CREATE SCHEMA, CREATE TABLE, CREATE
INDEX and CREATE SEQUENCE are supported, and in each of them there are
some things missing).  This is code tedious to write but not difficult.

To see this stuff in action, an event trigger function such as this is
useful:

CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
        r RECORD;
BEGIN
        FOR r IN SELECT * FROM pg_event_trigger_get_creation_commands()
        LOOP
                RAISE NOTICE 'JSON blob: %', r.command;
                RAISE NOTICE 'expanded: %', pg_event_trigger_expand_command(r.command::json);
        END LOOP;
END;
$$;

CREATE EVENT TRIGGER snitch ON ddl_command_end
when tag in ('create schema', 'create table', 'create index', 'create sequence')
EXECUTE PROCEDURE snitch();

Then execute commands to your liking.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services