Re: Problems with stored procedure

----- Original Message -----
From: "lmanorders" <lmanorders@gmail.com>
To: <pgsql-novice@postgresql.org>
Sent: Wednesday, August 22, 2012 4:28 PM
Subject: Problems with stored procedure


> I'm trying to learn how to use stored procedures. In particular, I need to
> return multiple values from the function, so I've been experimenting with
> the OUT argument type. I'm writing code in C++ and using the libpq dll as
> the interface to postgresql. I've created the following function, that
> works:
>
> CREATE OR REPLACE FUNCTION getdetailamts ( acct_no char(22), beg_moyr
> char(6),
>      end_moyr char(6), OUT beg_bal float8) AS $$
>  DECLARE sum_totl float8;
>  BEGIN
>    SELECT SUM(dramt) FROM detrec WHERE detrec.acctno = $1 AND
> detrec.apmoyr
> >= $2 AND
>               detrec.apmoyr <= $3 INTO sum_totl;
>    beg_bal := sum_totl;
>  END; $$ language plpgsql;
>
> This returns the proper value, but when I attempt to add a second argument
> of OUT type, I get an error when attempting to create the function:
>
> CREATE OR REPLACE FUNCTION getdetailamts ( acct_no char(22), beg_moyr
> char(6),
>      end_moyr char(6), OUT beg_bal float8, OUT half_bal float8) AS $$
>  DECLARE sum_totl float8;
>  BEGIN
>    SELECT SUM(dramt) FROM detrec WHERE detrec.acctno = $1 AND
> detrec.apmoyr
> >= $2 AND
>               detrec.apmoyr <= $3 INTO sum_totl;
>    beg_bal := sum_totl;
>    half_bal := sum_totl / 2;
>  END; $$ language plpgsql;
>
> Can anyone tell me why adding the second OUT argument type causes the
> function to return an error and not be created?
>
> Thanks, Lynn
>
I retyped this function and tried it again, and now the function is working.
I must have missed something somewhere. I've been staring at it for about 4
hours and couldn't get it to work, but now it does. Sorry for the false
alarm.

Thanks, Lynn