Re: How to debug efficiently

On 3 Jun 2010, at 12:43, Jamie Lawrence-Jenner wrote:

> Hi All
>
> In SQL Server I could copy sql code out of an application and paste it into SSMS, declare & assign vars that exist in
thesql and run.. yay great debugging scenario. 
>
> e.g. (please note I am rusty and syntax may be incorrect)
>
> declare @x as varchar(10)
> set @x = 'abc'
> select * from sometable where somefield = @x
>
> I want to do something simular with postgres in pgadmin3 (or another postgres tool, anyy reccomendations?)

I don't use pgadmin, I usually use psql.

If it gets complicated enough that it's inconvenient to use from the psql prompt, I usually just whip up a quick script
totest stuff like this in. 
I often keep those in a tests directory using a descriptive name for what the script tests, so that co-workers can
easilysee what they are. 

So far that's usually been PHP (for work), but I have the feeling that I could create something that'd be usable from
withinthe Python interpreter prompt, allowing to adjust definitions while you get further in your debugging session. 

> I realise you can create pgscript, but it doesn't appear to be very good, for example, if I do the equlivent of
above,it doesn't put the single quotes around the value in @x, nor does it let me by doubling them up and you don't get
atable out after - only text... 

Never heard of pgscript, is that something that pgadmin provides? I have no idea how it interprets parameter values or
howit outputs query results, so I don't understand what problems you're seeing. 

> Currently I have a peice of sql someone has written that has 3 unique varibles in it which are used around 6 times
each...

It's probably easiest to create a prepared statement from that query and execute it with different parameter values.

It depends on what you're debugging though; if that's a performance problem, then turning the query into a prepared
statementcould (probably would) change the behaviour. 

> So the question is how do other people debug sql this sql EFFICIENTLY, preferably in a simular fashion to my sql
serverdays. 

I usually find the psql prompt more efficient to work with than, for example, pgadmin. But I'm a typical command line
user;What's efficient for one may not be for someone else. 

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4c078fa710151658735671!