Thread: Is there a way to view a rewritten query?

I'm trying to set up some complex rules and having a hard time figuring
out why they won't work. Being able to see the query after all the rules
have been applied would be a huge help, but I can't seem to find a way
to do this. log_statement only shows the original. debug_print_rewritten
works, but displays it in such a way as to be virtually unusable.
someone please tell me there's a simple way of doing this cause i'm
ready to take a hammer to my computer.

thanks

On Thu, Dec 02, 2004 at 09:32:35PM -0800, Jonathan Knopp wrote:
> I'm trying to set up some complex rules and having a hard time figuring
> out why they won't work. Being able to see the query after all the rules
> have been applied would be a huge help, but I can't seem to find a way
> to do this. log_statement only shows the original. debug_print_rewritten
> works, but displays it in such a way as to be virtually unusable.
> someone please tell me there's a simple way of doing this cause i'm
> ready to take a hammer to my computer.
>
One rather clumsy way to do it is to have a script which drops the
whole query and rule set and then recreates it.  I do this when
creating views.

I have a separate window open with my editor showing the script in
question, nicely formatted.  In the psql window I can then just \i the
script and see the diagnostics, then go back to the edit window and
edit the original and try again.

--
Chris Green (chris@areti.co.uk)

    "Never ascribe to malice, that which can be explained by incompetence."

Jonathan Knopp <pgsql@delegated.net> writes:
> I'm trying to set up some complex rules and having a hard time figuring
> out why they won't work. Being able to see the query after all the rules
> have been applied would be a huge help, but I can't seem to find a way
> to do this. log_statement only shows the original. debug_print_rewritten
> works, but displays it in such a way as to be virtually unusable.

Sorry, but that's all there is.  The rewriter isn't a source-to-source
transformation.

(If you feel like hacking, it might be interesting to try applying
the ruleutils.c functions to the rewritten querytree, but I'm quite
unsure how readable the result will be.)

            regards, tom lane