when using a bound cursor, error found..

From: 윤동수 <dsyoon(at)metasoftworks(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: when using a bound cursor, error found..
Date: 2005-03-29 11:46:44
Message-ID: 002801c53454$fb4864d0$2d216b3d@notebook04
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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;

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2005-03-29 11:55:55 Re: Executing Anonymous Blocks
Previous Message Neil Conway 2005-03-29 07:59:44 Re: Executing Anonymous Blocks