Thread: 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!

On 6/3/2010 5:43 AM, 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 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...

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

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

by pgscript I take you  meaning pl/pgsql   which unlike Transact-SQL is actually useful.

to debug in PG with pgadmin we have

http://pgfoundry.org/projects/edb-debugger/

that makes debugging pl/pgsql very easy and it works with pgadmin and

http://www.sqlmaestro.com/products/postgresql/maestro/tour/pgsql_debugger/

Your little example would like so in pl/pgsql
-------------------------------------------------------------
Create or Replace function MyTest()
returns integer AS
$BODYOFFUNCTION$

declare
x text = 'abc';

Begin

perform (select * from sometable where somefield = x);

end;
return 1 ;
$BODYOFFUNCTION$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.