Re: call stored function from ecpg w/cursor

Michael Fuhr wrote:
> On Tue, May 01, 2007 at 03:03:58AM -0400, Andrew Jarcho wrote:
>
>> Thank you for your continued help. A stripped down but fully
>> (mal)functioning version of the code is attached. The error, according
>> to the logfile ecpg_debug.log, occurs in the file callProcsViaC.pgc and
>> is clearly marked there. A lot of the included code is just for
>> completeness/reference.
>>
>
> The ms_getInvolvedInCase() function returns a composite type.
> Instead of
>
> EXEC SQL DECLARE c_1 CURSOR FOR SELECT ms_getInvolvedInCase(:mstsCaseID);
>
> try
>
> EXEC SQL DECLARE c_1 CURSOR FOR SELECT * FROM ms_getInvolvedInCase(:mstsCaseID);
>
>
Hi Michael--
Thank you very much for your kind assistance; that solved the problem,
which has occupied me all day for several days.

I made a change to the code, however, and although it now appears to
work correctly, it produces an error very similar to the one I was
getting before. It also produces a warning on compile. The error, raised
by ECPGdebug, is -202 'Too few arguments in line 51'. The compile-time
warning, cut and pasted from the output, is:

WARNING: cursor `c_1´ has been declared but ot opened (sic).

The change I made was to alter the function stored in the db so that it
returns a refcursor, and adjust my ecpg code accordingly. The cursor is
declared using:

EXEC SQL DECLARE c_1 CURSOR FOR SELECT
ms_getInvolvedInCase(:mstsCaseID, refcursor);

and instantiated using:

EXEC SQL SELECT ms_getInvolvedInCase(:mstsCaseID, 'c_1');

The stored function is coded as

CREATE OR REPLACE FUNCTION ms_getInvolvedInCase (cID INTEGER,
REFCURSOR) RETURNS REFCURSOR AS $$

BEGIN
OPEN $2 FOR
SELECT ACR.logname, A.lastName, A.firstName, ACR.role
FROM ms_account A, ms_accountCaseRole ACR
WHERE ACR.caseID = cID AND
ACR.logname = A.logname;

RETURN $2;

END;

$$ LANGUAGE plpgsql;

and tuples are retrieved using:

EXEC SQL FETCH NEXT FROM c_1 INTO :lognm:lognm_ind,
:lastnm:lastnm_ind, :firstnm:firstnm_ind,
:rol:rol_ind;

inside a loop.

I'm afraid to use this code (updated version attached) because of the
error messages, although it works. As before, I'm compiling with gcc,
and linking with g++, on a Solaris UNIX system running PostgreSQL 8.0.3.
I'd appreciate any help you can give me.
Yours,
--Andy J.