Re: debugging plpgsql functions

Chris wrote:
> Hi all,
>
> I have a few plpgsql functions to debug to see why they are slow.
>
> They consist of a bunch of sql statements using new.* / old.*
> variables (ie not using "EXECUTE", the sql is being called directly).
>
> Is there a way to capture the actual sql that's being executed with
> variables substituted in, or even an easy way to log the variables
> being used?
>
> I couldn't see anything in the manual but maybe I missed something -
> any pointers appreciated.
>
> I tried http://pgfoundry.org/projects/edb-debugger/ but after loading
> the module, the functions wouldn't run properly (can't remember the
> error message but I can do it again if need be).
Debugging sql functions is sometimes a not to fun event. pgdebugger
has some gotchas that will bite you big time. One big item don't run
pgdebugger in a production machine i've had lock process and its done
some other odd things. I normally run the pgdebugger on a windows
install as it will install it for you. and use pgadmin two step through
the code.

One option use Raise Notice to see whats in a variable example
RAISE NOTICE 'Var1 %, Var2 %, ' , MemoryVar1, MemoryVar2 ;

then look whats returned to the client. Again i use pgAdmin for this as
it shows me all the messages sent from the server and keeps a nice easy
to read history.