From: | 윤동수 <dsyoon(at)metasoftworks(dot)com> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | how can I use a bound cursor |
Date: | 2005-03-28 03:54:18 |
Message-ID: | 000c01c53349$d10c98a0$2d216b3d@notebook04 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
----------------------------------------------------------------
-- I ' ll use a bound cursor, bun when I execute following cursor
-- I met a error message within for loop
-- Help me...
----------------------------------------------------------------
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
-------------------------------------------------
-- 커서처리
-------------------------------------------------
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 '처리완료';
END;
$BODY$ LANGUAGE plpgsql;
From | Date | Subject | |
---|---|---|---|
Next Message | Jaime Casanova | 2005-03-28 04:12:27 | Re: rewriter in updateable views |
Previous Message | Alvaro Herrera | 2005-03-28 03:12:46 | Re: Making oidvector and int2vector variable-length |