Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

From docs - 37.3.1.1. "A First Rule Step by Step"

CREATE TABLE shoelace_log (
    sl_name    text,          -- shoelace changed
    sl_avail   integer,       -- new available value
    log_who    text,          -- who did it
    log_when   timestamp      -- when
);

CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
    WHERE NEW.sl_avail <> OLD.sl_avail
    DO INSERT INTO shoelace_log VALUES (
                                    NEW.sl_name,
                                    NEW.sl_avail,
                                    current_user,
                                    current_timestamp
                                );

Now someone does:

(1) UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';

And the parser generates this additional query

(2) INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data
 WHERE 6 <> shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';

The question is: Are there any tools to tell how the query (1) gets rewritten into (1) + (2)?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

There is no direct way to see an SQL representation of the rewritten query, because the rewriting happens on an internal tree representation, and it's not easy to turn this back into SQL. The closest thing is turning on the configuration parameter debug_print_rewritten, which prints a representation of that internal tree format to the server log. If you use this in conjunction with the settings debug_print_parse and debug_print_plan (and possibly debug_pretty_print), you can see how the query is transformed through the various stages. The format isn't easy to read, but if you are interested in learning the details of this, it will probably be worth it.

share|improve this answer
    
I've also learned that EXPLAIN gives information on the number and type of queries involved (and is far more readable than database's debug log :). –  hegemon Nov 17 '11 at 20:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.