Thread: how to test my stored procedures?

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.

OUT parameters get returned as part of a record.

CREATE FUNCTION foo(a int4, OUT b int4, OUT c int4) AS $$
BEGIN
   b:=a+1;
   c:=a+2;
END; $$ LANGUAGE plpgsql;


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

HTH
--
   Richard Huxton
   Archonet Ltd

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

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.
>
> i want to test from a query tool.
> i can do with procedures without out paramters,
> but how to achive with out parameters?
> can i declare a variable out of procedure and
> then send it as an out parameter and see it is value?
>
> thanks for your help.

I test mine from the query editor.

PG Lightning Admin also has a nice function editor that has a run
button, the run button will then prompt you for any input params that
need values, then create the sql for you to run in the query editor.

Of course you can do this manually.

If your function has out params call it from the query editor just like
you would call a table.

select * from myproc();

You can then see the results.

--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql
http://www.amsoftwaredesign.com