pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwhen using a bound cursor, error found...
2+ messages / 2 participants
[nested] [flat]
* when using a bound cursor, error found...
@ 2005-03-29 11:49 À±µ¿¼ö <dsyoon@metasoftworks.com>
0 siblings, 1 reply; 2+ messages in thread
From: À±µ¿¼ö @ 2005-03-29 11:49 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: when using a bound cursor, error found...
@ 2005-03-30 07:29 Michael Fuhr <mike@fuhr.org>
parent: À±µ¿¼ö <dsyoon@metasoftworks.com>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2005-03-30 07:29 UTC (permalink / raw)
To: dsyoon@metasoftworks.com; +Cc: pgsql-sql
On Tue, Mar 29, 2005 at 08:49:49PM +0900, ?????? 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.
It's usually a good idea to post the error message. In most cases
it should say what's wrong, or at least where something's wrong.
> DECLARE
> p_param1 VARCHAR;
> p_param1 VARCHAR;
You've declared the same variable twice; the second declaration
should be p_param2.
> OPEN cur_test(p_param2);
>
> for rec_test in cur_test loop
I don't think you can iterate over a cursor this way. Rather than
use an explicit cursor, why not use "FOR rec_test IN SELECT ..."?
FOR loops automatically use cursors so you don't have to open one
yourself. But if you want to use a cursor then you could do
something like this:
OPEN cur_test(p_param2);
LOOP
FETCH cur_test INTO rec_test;
EXIT WHEN NOT FOUND;
-- rest of code
END LOOP;
CLOSE cur_test;
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-03-30 07:29 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-29 11:49 when using a bound cursor, error found... À±µ¿¼ö <dsyoon@metasoftworks.com>
2005-03-30 07:29 ` Michael Fuhr <mike@fuhr.org>
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