Thread: 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_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

Igor Shevchenko <igor@carcass.ath.cx> writes:
> -- all other queries are sent using PQsendQueryParams

> -- 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;

Hm.  That is not going to work, since the bound parameter only lasts for
the execution of the query (ie, the DECLARE).

Possibly someone could look into saving the parameters presented during
DECLARE along with the state of the cursor, but don't hold your breath
...

            regards, tom lane