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_message_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_cursor'::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
