473,883 Members | 2,361 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_fold er(v_folder_id, v_message_id,fa lse);
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 PQsendQueryPara ms
begin work;

-- the bound parameter for $1 is sent with this query
declare search_messages _cursor no scroll cursor for select
folder_msg.mess age_id from folder_msg where folder_msg.fold er_id=7866 and
folder_msg.mail _subj ilike $1;

select add_messages_to _folder(7871,'s earch_messages_ cursor'::refcur sor);

pg fails on this query with:
PGRES_FATAL_ERR OR: ERROR: no value found for parameter 1
CONTEXT: PL/pgSQL function "add_messages_t o_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
2 2509
Igor Shevchenko <ig**@carcass.a th.cx> writes:
-- all other queries are sent using PQsendQueryPara ms -- the bound parameter for $1 is sent with this query
declare search_messages _cursor no scroll cursor for select
folder_msg.mess age_id from folder_msg where folder_msg.fold er_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*******@postg resql.org

Nov 23 '05 #2
Igor Shevchenko <ig**@carcass.a th.cx> writes:
-- all other queries are sent using PQsendQueryPara ms -- the bound parameter for $1 is sent with this query
declare search_messages _cursor no scroll cursor for select
folder_msg.mess age_id from folder_msg where folder_msg.fold er_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*******@postg resql.org

Nov 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
1822
by: Francis Lavoie | last post by:
Hello I have some questions regarding webframework, I must say that I quite lost and these questions are basicly to help me understand the way it work. I have some knowledge with PHP and JSP. I have looked for a python web framework to build a web site, a site that I had start in php (and quite finish), but for some reason I wont explain, I lost everything. I have started writting app with python 6
3
2219
by: Samuel | last post by:
I wrote a very simple httpmodule and tried to compile it with no success. This is my code: ============== Imports System Imports System.Web Imports Microsoft.VisualBasic NameSpace myErrorHandler
0
828
by: Igor Shevchenko | last post by:
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 '
8
2259
by: Roger | last post by:
I have a question regarding the behaviour of sql with OR and fetch first 1 rows only : I have a table with data : ACNO NAME TELNO CITY ZIP 1000 tim 8887778888 denver 1000 tim 8887778888 denver 56465
1
1292
by: DLN | last post by:
Hello all, I have a quick question regarding how best to use static strings in my C# code that I'm hoping someone can help me with. Is there any advantage/disadvantage from a performance standpoint to declaring all my string constants up-front as opposed to declaring them in-line? For example: public class ProjectConsts {
4
2315
by: Benny Van | last post by:
Hi all! I have a question regarding a windows operating system function: I was asked to write a small program for a homework to display the user name and computer name and the system time out to a console window: the display would be like: Hello XXX(user) Today is XXX(date) The Time is XXX(current system time) I was asked to use a Windows System Call---void GetLocalTime(SYSTIME
2
1827
by: Renji Panicker | last post by:
I have a question regarding references. Consider the following code: <code> #include <iostream> class A { int _a1; int _a2; int _a3; };
10
1915
by: somenath | last post by:
Hi All, I have one question regarding return value cast of malloc. I learned that we should not cast the return value of malloc because it is bug hider. But my question is as mentioned bellow . Lets say I have not included stdlib.h in my program still I am using malloc so compiler will throw warring because with out prototype
8
1710
by: Goran | last post by:
Hi all, I have a question regarding operator <<. A lib of mine contains a class with an overloaded operator << as NON- class member. This would look like: #include <iostream> #include <libsomelib.h>
0
9953
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11166
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10768
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10868
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10422
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9588
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6009
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4231
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3242
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.