Re: Problems with stored procedure

On 23/08/12 10:54, lmanorders wrote:
> ----- 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
>
>
>
>
No worries!

I remember when I was learning C many years ago, being stuck for several
hours wondering why a short program of less than 2/3 of a page was not
working as expected... turned out I had an extraneous semi-colon after a
while statement:

while (condition);
{
     do something
}

This was despite having extensive experience in FORTRAN & COBOL - so I
wasn't exactly a novice programmer!