Re: how to test my stored procedures?

On 6/15/06, Richard Huxton <dev(at)archonet(dot)com> wrote:
> Yavuz Kavus wrote:
> > hi everybody.
> >
> > i am writing stored procedures.
> > lots of them have out parameters.
> >
> > i dont know how to test them
> > from an sql query tool(ex: pgAdmin query tool).
> > i am trying to test them from my program, but it is not so efficient.

Stored procedures are very amenable to regression testing. First, some tips.
1. Do not write your SP code directly into psql or pgadmin but instead
into .sql files which you copy/paste into your tool of choice (i
prefer psql). This also provides the benefit of interacting well with
souce control systems.

2. Make a small regression database or a copy of your production
database you would like to run tests on. Make another .sql file (call
it regression) which recreates your stored procedores by making use of
pqsl and then runs them giving expected results from your controlled
database\i:

e.g.
-- regression:
\i ../procs/foo.sql
\i ../procs/bar.sql

select foo(1,2) = 3;
select bar(4,6) = 24;

run your regression script daily on cron with a scan that checks for errors.

> richardh=# SELECT foo(9);
> foo
> ---------
> (10,11)

you can also do

select * from foo(9);
to explode the record and get proper columns.

Merlin