How can I see a query after rules have been applied?

Question

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)?

Answer 1

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.