From: À±µ¿¼ö <dsyoon@metasoftworks.com>
To: pgsql-sql@postgresql.org
Subject: when using a bound cursor, error found...
Date: Tue, 29 Mar 2005 20:49:49 +0900
Message-ID: <008b01c53455$6992a590$2d216b3d@notebook04> (raw)
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dsyoon@metasoftworks.com
Subject: Re: when using a bound cursor, error found...
In-Reply-To: <008b01c53455$6992a590$2d216b3d@notebook04>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox