Re: How to debug efficiently

[Justin Graf <justin@xxxxxxxxxxxx>]

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.

begin:vcard
fn:Justin Graf
n:Graf;Justin
org:Magwerks Corp
adr:;;501 Commerce Drive;Danville ;IN;46122;USA
email;internet:justin@xxxxxxxxxxxx
tel;work:317-241-8011 ext 703
tel;fax:317-241-8015
x-mozilla-html:FALSE
url:www.magwerks.com
version:2.1
end:vcard

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general