Home > mailing lists

Thread: when using a bound cursor, error found..

when using a bound cursor, error found..

From

윤동수

Date:

29 March 2005, 15:46:57

I 'll use a bound cursor with parameters.

But when I use such a cursor, I found a error.

I don't know error message.

How can I use a bound cursor.

in following sample, near a 'for loop' , error found.

------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION cursor_test(

vp_param1 VARCHAR
,vp_param2 VARCHAR

)

RETURNS VARCHAR AS $BODY$

DECLARE

p_param1 VARCHAR;
p_param1 VARCHAR;

cur_test CURSOR (c_param VARCHAR) IS
SELECT col_1, col_2, col_3 FROM tab_1 WHERE col_1 = c_param;

rec_test RECORD;

v_count NUMERIC;

BEGIN

p_param1 := nullif(vp_param1 , '');
p_param2 := nullif(vp_param2 , '');

IF p_param1 = 'txn' THEN

       -------------------------------------------------
       -- cursor
       -------------------------------------------------
       OPEN cur_test(p_param2);

for rec_test in cur_test loop

         --FETCH cur_test INTO rec_test;

         SELECT count(*) INTO v_count FROM tab_2 WHERE col_1 = rec_test.col_1 and col_2 = rec_test.col_2 ;

         IF v_count >0 THEN

           insert into tab_2
           (
             col_1
           , col_2
           , col_3
           )
           values
           (
             rec_test.col_1
           , rec_test.col_2
           , rec_test.col_3
           );

         END IF;

       end loop; -- cur_test

CLOSE cur_test;

    END IF;

    RETURN 'txn OK';

END;
$BODY$ LANGUAGE plpgsql;

Re: when using a bound cursor, error found..

From

Jaime Casanova

Date:

31 March 2005, 07:06:08

On Tue, 29 Mar 2005 20:46:44 +0900, 윤동수 <dsyoon@metasoftworks.com> wrote:
> I 'll use a bound cursor with parameters.
> But when I use such a cursor, I found a error.
> I don't know error message.
>
How do you know there is an error. Postgres should send you a message
or a log entry. without it i doubt can help you.

regards,
Jaime Casanova