Re: Add CREATE support to event triggers

On Thu, Nov 21, 2013 at 2:36 AM, Christopher Browne <cbbrowne(at)gmail(dot)com> wrote:
>> b) What's the best design of the SRF output? This patch proposes two
>> columns, object identity and create statement. Is there use for
>> anything else? Class/object OIDs perhaps, schema OIDs for objects types
>> that have it? I don't see any immediate need to that info, but perhaps
>> someone does.
>
> Probably an object type is needed as well, to know if it's a table or
> a domain or a sequence or whatever.
>
> I suspect that what will be needed to make it all usable is some sort of
> "structured" form. That is in keeping with Robert Haas' discomfort with
> the normalized form.
>
> My "minor" gripe is that you haven't normalized enough (e.g. - it should be
> CREATE TABLE foo.foo2 (acolumn foo.bar), capturing the normalization of
> data types that are referenced).
>
> But Robert's quite right that users may want more than just to capture that
> literally; they may want to modify it, for instance, by shifting to another
> schema. And it will be no fun at all if you have to construct an SQL parser
> in order to change it.

It's certainly much easier to transform a structured representation
into a valid SQL command string than it is to do the inverse.

You may be interested in an extension that I'm working on for a
client, which provides relation_create, relation_alter, and
relation_drop event triggers for 9.3:

https://bitbucket.org/malloclabs/pg_schema_triggers

I decided to create a composite type for each event, which can be
accessed from within the event trigger by calling a special function.
For example, the relation_alter event supplies the relation Oid and
the "old" and "new" pg_class rows. It's easy to then examine the old
vs. new rows and determine what has changed.

Kind regards,
Andrew Tipton