Re: Problems with stored procedure

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: lmanorders <lmanorders(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Problems with stored procedure
Date: 2012-08-22 23:03:03
Message-ID: 50356527.6080904@archidevsys.co.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On 23/08/12 10:54, lmanorders wrote:
> ----- Original Message ----- From: "lmanorders" <lmanorders(at)gmail(dot)com>
> To: <pgsql-novice(at)postgresql(dot)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!

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message lmanorders 2012-08-23 21:08:18 (More) Questions about stored procedures
Previous Message lmanorders 2012-08-22 22:54:51 Re: Problems with stored procedure