Re: Command Triggers - Mailing list pgsql-hackers

On Sun, Dec 18, 2011 at 5:11 PM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> I guess XML would be ok but we don't embed powerful enough tools, and
> JSON might be perfect but we would need to have a full blown datatype
> and functions to work with that from plpgsql.  What other tree-ish data
> type can we have?
>
> EXPLAIN is already able to spit out XML and JSON (and YAML) but the
> typical client consuming that output is not running as a backend stored
> procedure, so I guess that's not a precedent and we still need something
> with a good support (type, operators, walking functions…) to back it.

Right.  If we're actually going to expose the parse tree, I think JSON
(or even XML) would be a far better way to expose that than the
existing nodeToString() output.  Sure, you could make due with the
nodeToString() output for some things, especially in PL/perl or
PL/python.  But JSON would be far better, since it's a standard format
rather than something we just made up, and could be used in PL/pgsql
as well, given proper support functions.

Another option would be to do something like this:

CREATE TYPE pg_trigger_on_create_table AS (   catalog_name text,   schema_name text,   relation_name text,   ...
);

That's not materially different from exposing the parse tree, but it's
more convenient for PL/pgsql and doesn't require adding a new datatype
like JSON.  It might require an awful lot of tuple-construction code
and datatype definitions, though.

Still another option would be to expose some of this information
through "magical" variables or functions, sort of like the way that
declaring a function to return trigger causes it to have NEW and OLD.
It could have STUFF.catalog_name, STUFF.schema_name,
STUFF.relation_name, or whatever we want.

None of these approaches really get around the fact that if the
command syntax changes, the trigger API has to change, too.  You might
be able to get around that for CREATE commands by having only AFTER
triggers, and just passing the OID; and for DROP commands by having
only BEFORE triggers, and just passing the OID.  But I don't see any
way to make it work very well for ALTER commands.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company