471,092 Members | 1,561 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

question regarding cursors in plpgsql, declare and bound params (v3 protocol)

Hi.

I've got a problem with using cursor in a plpgsql function. Cursor is created
via DECLARE, it's SELECT query has placeholders. I use PostgreSQL 7.4.2.

Here's a problematic plpgsql function:

create or replace function add_messages_to_folder(integer,refcursor) returns
integer security definer
as '
declare
v_folder_id alias for $1;
v_c alias for $2;
v_message_id integer;
v_added_nr integer := 0;
begin

LOOP
FETCH v_c INTO v_message_id;
EXIT WHEN NOT FOUND;
SELECT INTO v_added_nr
v_added_nr+add_message_to_folder(v_folder_id,v_mes sage_id,false);
END LOOP;
UPDATE folder set obj_nr=obj_nr+v_added_nr where id=v_folder_id;
CLOSE v_c;
RETURN v_added_nr;
END;
' language 'plpgsql';

It gets an opened cursor as it's 2nd parameter (type=refcursor) and tries to
read all data out of it via FETCH, but fails with an error message.
Here's an example of how it comes to an error. Commands are executed from my
client app via libpq with extended protocol:

-- all other queries are sent using PQsendQueryParams
begin work;

-- the bound parameter for $1 is sent with this query
declare search_messages_cursor no scroll cursor for select
folder_msg.message_id from folder_msg where folder_msg.folder_id=7866 and
folder_msg.mail_subj ilike $1;

select add_messages_to_folder(7871,'search_messages_curso r'::refcursor);

pg fails on this query with:
PGRES_FATAL_ERROR: ERROR: no value found for parameter 1
CONTEXT: PL/pgSQL function "add_messages_to_folder" line 9 at fetch

I've tried to send this query with and without bound parameter, with no luck
(same error message).

Can someone shed some light on this ? :)

--
Best regards,
Igor Shevchenko

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #1
0 753

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Francis Lavoie | last post: by
3 posts views Thread by Samuel | last post: by
4 posts views Thread by Benny Van | last post: by
2 posts views Thread by Renji Panicker | last post: by
10 posts views Thread by somenath | last post: by
8 posts views Thread by Goran | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.