Re: Deparsing DDL command strings

On 10/5/12 11:15 AM, Tom Lane wrote:
>> Also, we need to normalize that command string. Tools needing to look at
>> >it won't want to depend on random white spacing and other oddities.
> Instead, they'll get to depend on the oddities of parse transformations
> (ie, what's done in the raw grammar vs. what's done in parse_analyze),
> which is something we whack around regularly. Besides which, you've
> merely asserted this requirement without providing any evidence that
> it's important at all, let alone important enough to justify the kind of
> maintenance burden that you want to saddle us with.

I definitely want to be able to parse DDL commands to be able to either enforce things or to drive other parts of the system based on what's changing. Without the ability to capture (and parse) DDL commands I'm stuck creating wrapper functions around anything I want to capture and then trying to ensure that everyone uses the wrappers and not the raw DDL commands.

Event triggers that just spit out raw SQL give me the first part of this, but not the second part: I'm still stuck trying to parse raw SQL on my own. Having normalized SQL to parse should make that a bit easier, but ideally I'd like to be able to pull specific elements out of a command. I'd want to be able to do things like:

IF command is ALTER TABLE THEN
FOR EACH subcommand
IF subcommand IS DROP COLUMN THEN
do something that needs to know what column is being dropped
ELSE IF subcommand IS ADD COLUMN THEN
do something that needs to know the definition of the column being added

I don't think every bit of that has to be dealt with by the event trigger code itself. For example, if you're adding a column to a table and the entries have already been made in the catalog, you could query to get the details of the column definition if you were given an OID into pg_attributes.

Having said all that, an event system that spits back the raw SQL would certainly be better than nothing. But realize that people would still need to do parsing on it (ie: replication solutions will need to know what table just got ALTER'd).
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net