jrodenhi wrote:

> Have you come up with any tricks for
> debugging stored procedures that help you a lot?

Besides what others noted, one mistake is common for beginners:

select something
from sometable
into :somevariable;

I case SELECT returns the empty set, the "somevariable" doesn't become
NULL, but it is left as it was. If you don't initialize it, you have
some random data there.

Therefore, make sure you initialize variables before "select ... into"
construct:

somevariable = 'default value';
select something
from sometable
into :somevariable;


Also, at some point I think everyone of us tried:

somevariable = (select ...);

That does not work, you need to "select ... into :somevariable".

> Do you do anything
> different with triggers?

Well, you have new. and old. relations avaialble, and with BEFORE
triggers you can change the values before they get written, etc. But,
PSQL is pretty much the same.

> Or, maybe a little simpler, do you have a
> simple rule for when you use a colon with a variable and when you don't?

When it's part of SQL statement, use it. Otherwise, don't.

--
Milan Babuskov
http://swoes.blogspot.com/
http://www.flamerobin.org