From: | "lmanorders" <lmanorders(at)gmail(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Problems with stored procedure |
Date: | 2012-08-22 22:54:51 |
Message-ID: | 5D583E2AAD6F4389839816B4F76D3252@LynnPC |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
----- 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
From | Date | Subject | |
---|---|---|---|
Next Message | Gavin Flower | 2012-08-22 23:03:03 | Re: Problems with stored procedure |
Previous Message | lmanorders | 2012-08-22 22:28:55 | Problems with stored procedure |