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 the sql 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 to test 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 easily see 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 within the 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 a table out after - only text...

Never heard of pgscript, is that something that pgadmin provides? I have no idea how it interprets parameter values or how it 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 statement could (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 server days.

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!