Re: how to test my stored procedures? - Mailing list pgsql-general

From Merlin Moncure
Subject Re: how to test my stored procedures?
Date
Msg-id b42b73150606150451q1b0a0bb1k4cba469ea8874b9d@mail.gmail.com
Whole thread Raw
In response to Re: how to test my stored procedures?  (Richard Huxton <dev@archonet.com>)
List pgsql-general
On 6/15/06, Richard Huxton <dev@archonet.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

pgsql-general by date:

Previous
From: Michael Glaesemann
Date:
Subject: Re: Relation "tablename" does not exist
Next
From: "JahMic"
Date:
Subject: Annoying "could not find a "psql" to execute" message