467,912 Members | 1,338 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,912 developers. It's quick & easy.

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
  • viewed: 2271
Share:
2 Replies
Igor Shevchenko <ig**@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #2
Igor Shevchenko <ig**@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #3

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 this site, you agree to our Privacy Policy and Terms of Use.