Re: How to debug efficiently

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.