Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group