From: | "lmanorders" <lmanorders(at)gmail(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Problems with stored procedure |
Date: | 2012-08-22 22:28:55 |
Message-ID: | 26CEDE87A7E14D56BCDA26A0CE6550A1@LynnPC |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | lmanorders | 2012-08-22 22:54:51 | Re: Problems with stored procedure |
Previous Message | Tom Lane | 2012-08-22 14:51:08 | Re: Vacuum error message |