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

when using a bound cursor, error found..

From
윤동수
Date:
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:
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