Re: Add CREATE support to event triggers

On Thu, Nov 21, 2013 at 2:36 AM, Christopher Browne <cbbrowne@gmail.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