Re: [GENERAL] cursor declare

[Tom Lane]

Peter Koukoulis  writes:
> This is my first cursor attempt:

> according to docs

> DECLARE
> curs1 refcursor;
> curs2 CURSOR FOR SELECT * FROM tenk1;
> curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

> this should work, but getting error:

> ft_node=# declare cur_test1 CURSOR (key integer) for select * from test1
> where x=key;
> ERROR:  syntax error at or near "("
> LINE 1: declare cur_test1 CURSOR (key integer) for select * from tes...

It looks like you're trying to use the plpgsql syntax for a cursor
variable as part of a DECLARE CURSOR SQL-level command.  They're not
the same thing at all.  In particular, there isn't any concept of
parameters in the SQL DECLARE CURSOR command.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] cursor declare

[Peter Koukoulis]

Hi

This is my first cursor attempt:

according to docs

DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

this should work, but getting error:

ft_node=# declare cur_test1 CURSOR (key integer) for select * from test1
where x=key;
ERROR:  syntax error at or near "("
LINE 1: declare cur_test1 CURSOR (key integer) for select * from tes...

Table is defined as:
psql (9.6.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
Type "help" for help.

ft_node=# \d+ test1
Table "public.test1"
 Column | Type  | Modifiers | Storage  | Stats target |
Description
+---+---+--+--+-
 x  | integer   |   | plain|  |
 y  | character varying(20) |   | extended |  |


Can somebody please help?

P

[GENERAL] cursor return null

[Ramesh T]

Hello,
   in postgres function (id bigint ),the following code not return
any value with artNums ,But when i do  select statement ony it's output the
values with out include cursor

 i.e,cursor problem ..?

please let me know what should i do to getvalues from cursor, but i dont
where i missing

 function (id bigint )

DECLARE
  PartNums   varchar (1);
 artNums CURSOR for
  SELECT p.PART_NUM part_num
FROM lineitem sol, part p
   WHERE sol.ORDER_ID = id AND p.PART_ID = sol.PART_ID;

   BEGIN
  FOR cPart IN  artNums LOOP
   BEGIN
PartNums := PartNums || cPart.part_num || ', ';
END;

Re: [GENERAL] cursor return null

[David G Johnston]

rameshparnanditech wrote
 Hello,
in postgres function (id bigint ),the following code not return
 any value with artNums ,But when i do  select statement ony it's output
 the
 values with out include cursor
 
  i.e,cursor problem ..?
 
 please let me know what should i do to getvalues from cursor, but i dont
 where i missing
 
  function (id bigint )
 
 DECLARE
   PartNums   varchar (1);
  artNums CURSOR for
   SELECT p.PART_NUM part_num
 FROM lineitem sol, part p
WHERE sol.ORDER_ID = id AND p.PART_ID = sol.PART_ID;
 
BEGIN
   FOR cPart IN  artNums LOOP
BEGIN
 PartNums := PartNums || cPart.part_num || ', ';
 END;

You have two BEGIN statements and no END LOOP statement...you need to read
the documentation and learn the correct syntax for using CURSORS and FOR
loops.

You should also post the entire function and not just excerpts to minimize
the risk of copy/paste problems and not reporting your reality.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/cursor-return-null-tp5812219p5812246.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor Example Needed

[Perry Smith]

On Oct 28, 2013, at 6:50 PM, Adrian Klaver adrian.kla...@gmail.com wrote:

 On 10/28/2013 04:36 PM, Perry Smith wrote:
 
 On Oct 28, 2013, at 6:13 PM, John R Pierce pie...@hogranch.com wrote:
 
 On 10/28/2013 3:58 PM, Adrian Klaver wrote:
 The docs do a good job of illustrating:
 
 http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html
 
 thats for cursors created within a plpgsql function.
 
 I think what the OP wants is a top level cursor, which is a different 
 thing...
 
 see
 http://www.postgresql.org/docs/current/static/sql-declare.html
 http://www.postgresql.org/docs/current/static/sql-fetch.html
 http://www.postgresql.org/docs/current/static/sql-close.html
 
 the fetch page shows an example of the complete usage in the context of a 
 database transaction.
 
 Thank you to Merlin.  I now understand better where my confusion was.
 
 John:
 
 Those examples are great except there is no way that I know of to loop on
 the top level as you call it.  I'm trying to do something that I can give 
 to
 psql which will loop through the entire set that is produced.
 
 The FETCH example shows you how. You do not have FOR but you do have FORWARD 
 and if you DECLARE SCROLL, BACKWARD. If you need to do actions on each row as 
 it is fetched then you will probably need to do it in a function. Your 
 original post though was concerned with dealing with an out of memory error 
 caused by returning to large a result set at one time and that can be handled 
 in psql as illustrated.

Yes.  I finally understand your (or someone's) original reply.

Thank you to all who helped me out.

Perry




signature.asc
Description: Message signed with OpenPGP using GPGMail

[GENERAL] Cursor Example Needed

[Perry Smith]

When I execute the SELECT statement directly I get:

psql:table.sql:28: out of memory for query result

I've read the way around this is to use cursors.  So I read and I see that I 
can use a FOR statement but I need that inside a function.

So far, I've come up with this:

 CREATE OR REPLACE FUNCTION blah() RETURNS integer AS $$
 
 DECLARE
   xyz CURSOR FOR SELECT * FROM shipped_files WHERE aix_file = 
 '/usr/lib/drivers/vioentdd' AND service_pack = '7100-01-06';
 
 BEGIN
   FOR my_rec IN xyz LOOP
 -- xyxyxy what to do where? xyxyxy  These things don't work:
 COPY ( my_rec ) TO stdout;
 SELECT * FROM my_rec;
 etc.
   END LOOP;
 
   RETURN 5;
 END
 $$ LANGUAGE plpgsql;
 
 SELECT * FROM blah();

I just want to output the rows being selected as text.  Basically I want the 
same output that the SELECT statement would produce if it didn't fail.

Also, what should I do with the SELECT * FROM blah() statement?  I'm doing that 
just to get blah() to execute.  I have a feeling I'm way far off base.

Thank you,
Perry



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [GENERAL] Cursor Example Needed

[Merlin Moncure]

On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith pedz...@gmail.com wrote:
 When I execute the SELECT statement directly I get:

 psql:table.sql:28: out of memory for query result

psql will do this automatically if you tell it to:
http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html

cursors can work with your code, but they need participation from the
client side.  basically you stage the cursor then repeatedly FETCH
until done.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor Example Needed

[Perry Smith]

On Oct 28, 2013, at 4:11 PM, Merlin Moncure mmonc...@gmail.com wrote:

 On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith pedz...@gmail.com wrote:
 When I execute the SELECT statement directly I get:
 
 psql:table.sql:28: out of memory for query result
 
 psql will do this automatically if you tell it to:
 http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html
 
 cursors can work with your code, but they need participation from the
 client side.  basically you stage the cursor then repeatedly FETCH
 until done.

Ok.  I'll try that.  I'm still curious how to do it using cursors if anyone 
wants
to provide a sample.

Thank you,
Perry



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [GENERAL] Cursor Example Needed

[Adrian Klaver]

On 10/28/2013 02:27 PM, Perry Smith wrote:


On Oct 28, 2013, at 4:11 PM, Merlin Moncure mmonc...@gmail.com wrote:


On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith pedz...@gmail.com wrote:

When I execute the SELECT statement directly I get:

psql:table.sql:28: out of memory for query result


psql will do this automatically if you tell it to:
http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html

cursors can work with your code, but they need participation from the
client side.  basically you stage the cursor then repeatedly FETCH
until done.


Ok.  I'll try that.  I'm still curious how to do it using cursors if anyone 
wants
to provide a sample.


http://www.postgresql.org/docs/9.3/interactive/sql-fetch.html



Thank you,
Perry




--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor Example Needed

[Perry Smith]

On Oct 28, 2013, at 5:21 PM, Adrian Klaver adrian.kla...@gmail.com wrote:

 On 10/28/2013 02:27 PM, Perry Smith wrote:
 
 On Oct 28, 2013, at 4:11 PM, Merlin Moncure mmonc...@gmail.com wrote:
 
 On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith pedz...@gmail.com wrote:
 When I execute the SELECT statement directly I get:
 
 psql:table.sql:28: out of memory for query result
 
 psql will do this automatically if you tell it to:
 http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html
 
 cursors can work with your code, but they need participation from the
 client side.  basically you stage the cursor then repeatedly FETCH
 until done.
 
 Ok.  I'll try that.  I'm still curious how to do it using cursors if anyone 
 wants
 to provide a sample.
 
 http://www.postgresql.org/docs/9.3/interactive/sql-fetch.html

Yea, I looked at those.  Part of my original email got lost.  Here is from the 
original
email:

 CREATE OR REPLACE FUNCTION blah() RETURNS integer AS $$
 
 DECLARE
  xyz CURSOR FOR SELECT * FROM shipped_files WHERE aix_file = 
 '/usr/lib/drivers/vioentdd' AND service_pack = '7100-01-06';
 
 BEGIN
  FOR my_rec IN xyz LOOP
-- xyxyxy what to do where? xyxyxy  These things don't work:
COPY ( my_rec ) TO stdout;
SELECT * FROM my_rec;
etc.
  END LOOP;
 
  RETURN 5;
 END
 $$ LANGUAGE plpgsql;
 
 SELECT * FROM blah();

Perhaps my question is what do I do with my_rec once I have it?  I want
output the same as if I did the top SELECT statement directly.



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [GENERAL] Cursor Example Needed

[Adrian Klaver]

On 10/28/2013 03:49 PM, Perry Smith wrote:


On Oct 28, 2013, at 5:21 PM, Adrian Klaver adrian.kla...@gmail.com wrote:


On 10/28/2013 02:27 PM, Perry Smith wrote:


On Oct 28, 2013, at 4:11 PM, Merlin Moncure mmonc...@gmail.com wrote:


On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith pedz...@gmail.com wrote:

When I execute the SELECT statement directly I get:

psql:table.sql:28: out of memory for query result


psql will do this automatically if you tell it to:
http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html

cursors can work with your code, but they need participation from the
client side.  basically you stage the cursor then repeatedly FETCH
until done.


Ok.  I'll try that.  I'm still curious how to do it using cursors if anyone 
wants
to provide a sample.


http://www.postgresql.org/docs/9.3/interactive/sql-fetch.html


Yea, I looked at those.  Part of my original email got lost.  Here is from the 
original
email:


Well the point of the example at the above link is that you do not have 
to do it in a function:)





CREATE OR REPLACE FUNCTION blah() RETURNS integer AS $$

DECLARE
  xyz CURSOR FOR SELECT * FROM shipped_files WHERE aix_file = 
'/usr/lib/drivers/vioentdd' AND service_pack = '7100-01-06';

BEGIN
  FOR my_rec IN xyz LOOP
-- xyxyxy what to do where? xyxyxy  These things don't work:
COPY ( my_rec ) TO stdout;
SELECT * FROM my_rec;
etc.
  END LOOP;

  RETURN 5;
END
$$ LANGUAGE plpgsql;

SELECT * FROM blah();


Perhaps my question is what do I do with my_rec once I have it?  I want
output the same as if I did the top SELECT statement directly.


The docs do a good job of illustrating:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html






--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor Example Needed

[Merlin Moncure]

On Mon, Oct 28, 2013 at 5:49 PM, Perry Smith pedz...@gmail.com wrote:
 On Oct 28, 2013, at 5:21 PM, Adrian Klaver adrian.kla...@gmail.com wrote:

 On 10/28/2013 02:27 PM, Perry Smith wrote:

 On Oct 28, 2013, at 4:11 PM, Merlin Moncure mmonc...@gmail.com wrote:

 On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith pedz...@gmail.com wrote:
 When I execute the SELECT statement directly I get:

 psql:table.sql:28: out of memory for query result

 psql will do this automatically if you tell it to:
 http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html

 cursors can work with your code, but they need participation from the
 client side.  basically you stage the cursor then repeatedly FETCH
 until done.

 Ok.  I'll try that.  I'm still curious how to do it using cursors if anyone 
 wants
 to provide a sample.

 http://www.postgresql.org/docs/9.3/interactive/sql-fetch.html

 Yea, I looked at those.  Part of my original email got lost.  Here is from 
 the original
 email:

 CREATE OR REPLACE FUNCTION blah() RETURNS integer AS $$

 DECLARE
  xyz CURSOR FOR SELECT * FROM shipped_files WHERE aix_file = 
 '/usr/lib/drivers/vioentdd' AND service_pack = '7100-01-06';

 BEGIN
  FOR my_rec IN xyz LOOP
-- xyxyxy what to do where? xyxyxy  These things don't work:
COPY ( my_rec ) TO stdout;
SELECT * FROM my_rec;
etc.
  END LOOP;

  RETURN 5;
 END
 $$ LANGUAGE plpgsql;

 SELECT * FROM blah();

 Perhaps my question is what do I do with my_rec once I have it?  I want
 output the same as if I did the top SELECT statement directly.

pl/pgsql is a language for creating functions.   From within a
function, you can interact with the database directly or with the
calling query through returning data.  You can't really send data to
the client side (unless you count 'COPY' or RAISE NOTICE').  So
although pl/pgsql FETCH has similar mechanics to SQL fetch, they are
very different in that pl/pgsql FETCH is generally directed at
variables.

pl/pgsql FETCH is somewhat baroque; typically it's cleaner and easier
to just iterate with a vanilla FOR-IN loop.  The only reasons why I
use cursors inside functions any more that I can think of off the top
of my head are when you need fancy scrolling (say to cycle through a
result set multiple times) or to pass data around between different
functions (but that is mostly displaced by temp tables for large sets
and or record arrays for very small ones)

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor Example Needed

[John R Pierce]

On 10/28/2013 3:58 PM, Adrian Klaver wrote:

The docs do a good job of illustrating:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html


thats for cursors created within a plpgsql function.

I think what the OP wants is a top level cursor, which is a different 
thing...


see
http://www.postgresql.org/docs/current/static/sql-declare.html
http://www.postgresql.org/docs/current/static/sql-fetch.html
http://www.postgresql.org/docs/current/static/sql-close.html

the fetch page shows an example of the complete usage in the context of 
a database transaction.



--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor Example Needed

[Perry Smith]

On Oct 28, 2013, at 6:13 PM, John R Pierce pie...@hogranch.com wrote:

 On 10/28/2013 3:58 PM, Adrian Klaver wrote:
 The docs do a good job of illustrating:
 
 http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html
 
 thats for cursors created within a plpgsql function.
 
 I think what the OP wants is a top level cursor, which is a different thing...
 
 see
 http://www.postgresql.org/docs/current/static/sql-declare.html
 http://www.postgresql.org/docs/current/static/sql-fetch.html
 http://www.postgresql.org/docs/current/static/sql-close.html
 
 the fetch page shows an example of the complete usage in the context of a 
 database transaction.

Thank you to Merlin.  I now understand better where my confusion was.

John: 

Those examples are great except there is no way that I know of to loop on
the top level as you call it.  I'm trying to do something that I can give to
psql which will loop through the entire set that is produced.

I came across the FOR-IN loop but that needs a function.  But as Merlin
points out, the function is on the server side and I need the loop on the 
client side.

Perhaps the 

\set FETCH_COUNT 50 

solution that Merln point out originally is the only choice.

I feel like I've learned a lot even though it might not have been what
I was originally trying to learn :-)

Thank you again,
Perry



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [GENERAL] Cursor Example Needed

[Adrian Klaver]

On 10/28/2013 04:36 PM, Perry Smith wrote:


On Oct 28, 2013, at 6:13 PM, John R Pierce pie...@hogranch.com wrote:


On 10/28/2013 3:58 PM, Adrian Klaver wrote:

The docs do a good job of illustrating:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html


thats for cursors created within a plpgsql function.

I think what the OP wants is a top level cursor, which is a different thing...

see
http://www.postgresql.org/docs/current/static/sql-declare.html
http://www.postgresql.org/docs/current/static/sql-fetch.html
http://www.postgresql.org/docs/current/static/sql-close.html

the fetch page shows an example of the complete usage in the context of a 
database transaction.


Thank you to Merlin.  I now understand better where my confusion was.

John:

Those examples are great except there is no way that I know of to loop on
the top level as you call it.  I'm trying to do something that I can give to
psql which will loop through the entire set that is produced.


The FETCH example shows you how. You do not have FOR but you do have 
FORWARD and if you DECLARE SCROLL, BACKWARD. If you need to do actions 
on each row as it is fetched then you will probably need to do it in a 
function. Your original post though was concerned with dealing with an 
out of memory error caused by returning to large a result set at one 
time and that can be handled in psql as illustrated.




I came across the FOR-IN loop but that needs a function.  But as Merlin
points out, the function is on the server side and I need the loop on the
client side.

Perhaps the

\set FETCH_COUNT 50

solution that Merln point out originally is the only choice.

I feel like I've learned a lot even though it might not have been what
I was originally trying to learn :-)

Thank you again,
Perry




--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor fetch Problem.

[Amit kapila]

On Friday, December 28, 2012 10:58 AM Harry wrote:
 Hi,
 Kindly see the below attached statements related to Cursor Fetch Issue it's
 still residing as a process.

500  20222 31036 79 Dec27 ?16:22:31 postgres: user1 sampledb 
192.168.0.40[36022] FETCH
500  20829 31036 81 Dec27 ?16:18:48 postgres: user1 sampledb 
192.168.0.40[57591] FETCH
500  20867 31036 81 Dec27 ?16:09:33 postgres: user1 sampledb 
192.168.0.40[45316] FETCH
500  20870 31036 81 Dec27 ?16:09:12 postgres: user1 sampledb 
192.168.0.40[45343] FETCH


Not sure if FETCH has hanged due to some reason or some other problem due to 
which cancel or terminate backend is not working.
Can you provide a stacktrace of hanged backends? Also do let me know you 
Postgresql version and OS?

Just for test, can you connect a new backend with psql. verify the entry for 
same in pg_stat_activity. call terminate API for this session. check again if 
the new entry has gone from pg_stat_activity?

With Regards,
Amit Kapila.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor fetch Problem.

[Amit Kapila]

On Thursday, December 27, 2012 2:44 PM Harry wrote:
 Below is the Linux ps -ef | grep postgres output :-
 
 501  12163  5473  0 Dec19 ?00:00:00 postgres: enterprisedb
 sampledb 192.168.0.231[53991] ?EDB-SPL Procedure successfully completed
 501  12167  5473  0 Dec19 ?00:00:00 postgres: enterprisedb
 sampledb 192.168.0.231[53995] ?EDB-SPL Procedure successfully
 completed.
 
 Also, if i try to kill from OS the whole database gets shut down.

Have you checked server logs for any specific messages like below or any
other message after you call cancel/terminate
Backend API?

FATAL:  terminating connection due to administrator command
ERROR:  canceling statement due to user request

With Regards,
Amit Kapila.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor fetch Problem.

[Harry]

No any statements as u mentioned.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Cursor-fetch-Problem-tp5737915p5738120.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor fetch Problem.

[Harry]

Hi,
Kindly see the below attached statements related to Cursor Fetch Issue it's
still residing as a process.

500  20222 31036 79 Dec27 ?16:22:31 postgres: user1 sampledb
192.168.0.40[36022] FETCH
500  20829 31036 81 Dec27 ?16:18:48 postgres: user1 sampledb
192.168.0.40[57591] FETCH
500  20867 31036 81 Dec27 ?16:09:33 postgres: user1 sampledb
192.168.0.40[45316] FETCH
500  20870 31036 81 Dec27 ?16:09:12 postgres: user1 sampledb
192.168.0.40[45343] FETCH






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Cursor-fetch-Problem-tp5737915p5738099.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor fetch Problem.

[Amit Kapila]

On Thursday, December 27, 2012 11:51 AM Harry wrote:
 Hi Amit,
 Thanks for Reply.
 Kindly see my below output.
 16650;sampledb;11965;10;enterprisedb;;192.168.0.231;;53897;*
 2012-12-19
 11:39:48.234799+05:30;2012-12-19 11:39:53.288441+05:30;2012-12-19
 11:39:53.288441+05:30*;f;DECLARE
 BEGIN
 EXEC
 16650;sampledb;12156;10;enterprisedb;;192.168.0.231;;53983;*
 2012-12-19
 12:18:38.57709+05:30;2012-12-19 12:18:43.922301+05:30;2012-12-19
 12:18:43.922301+05:30*;f;DECLARE
 BEGIN
 EXEC
 16650;sampledb;13243;10;enterprisedb;Postgres Studio -
 Browser;192.168.0.180;;3907;2012-12-26
 16:35:45.753172+05:30;;2012-12-26 16:35:46.577723+05:30;f;IDLE

Above shows that first two sessions are running from last few days. 
I am interested to know what is the transaction state in first 2 sessions.
In current version that information is part of pg_stat_activity, but don't
know how to get in the version you are using.
If possible for you, get this information. If you are using Linux system the
try ps ax | grep postgres and show the output

 
 Also, tried to Kill it Firstly by using Cancel Backend and then
 Terminate
 Backend output showing True but still remaining as a process (i.e. in
 pg_stat_activity).

Are you aware whether there is actually such long query running in first 2
sessions.
If you are not interested in first 2 sessions, you can even use OS kill
command.

With Regards,
Amit Kapila.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor fetch Problem.

[Amit Kapila]

On Thursday, December 27, 2012 11:51 AM Harry wrote:
 Hi Amit,
 Thanks for Reply.
 Kindly see my below output.
 
 Also, tried to Kill it Firstly by using Cancel Backend and then
 Terminate
 Backend output showing True but still remaining as a process (i.e. in
 pg_stat_activity).

Can you check the server log and see if there is any of below the statements
in the log:

FATAL:  terminating connection due to administrator command
ERROR:  canceling statement due to user request

With Regards,
Amit Kapila.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor fetch Problem.

[Harry]

Below is the Linux ps -ef | grep postgres output :-

501  12163  5473  0 Dec19 ?00:00:00 postgres: enterprisedb
sampledb 192.168.0.231[53991] ?EDB-SPL Procedure successfully completed
501  12167  5473  0 Dec19 ?00:00:00 postgres: enterprisedb
sampledb 192.168.0.231[53995] ?EDB-SPL Procedure successfully completed.

Also, if i try to kill from OS the whole database gets shut down.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Cursor-fetch-Problem-tp5737915p5737997.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Cursor fetch Problem.

[Harry]

Hi All,

I am getting a problem i.e. in database while checking pg_stat_activity
faced issue statement Fetch all in unnamed portal is residing as process
for couple of days also, not able to kill them through pg_terminate_backend
function.
 



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Cursor-fetch-Problem-tp5737915.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor fetch Problem.

[Amit Kapila]

On Wednesday, December 26, 2012 5:12 PM Harry wrote:
 Hi All,
 
 I am getting a problem i.e. in database while checking pg_stat_activity
 faced issue statement Fetch all in unnamed portal is residing as
 process
 for couple of days also, 

How have you concluded, it stays for couple of days? 
pg_stat_activity will show last statement executed in backend. What is the
value of 'state' for that backend. 
It's better if you can send the output of pg_stat_activity for that backend.

 not able to kill them through pg_terminate_backend  function.
Can you try once pg_cancel_backend(pid) and then pg_terminate_backend.

With Regards,
Amit Kapila.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor fetch Problem.

[Harry]

Hi Amit,
Thanks for Reply.
Kindly see my below output.
16650;sampledb;11965;10;enterprisedb;;192.168.0.231;;53897;*2012-12-19
11:39:48.234799+05:30;2012-12-19 11:39:53.288441+05:30;2012-12-19
11:39:53.288441+05:30*;f;DECLARE
BEGIN
EXEC 
16650;sampledb;12156;10;enterprisedb;;192.168.0.231;;53983;*2012-12-19
12:18:38.57709+05:30;2012-12-19 12:18:43.922301+05:30;2012-12-19
12:18:43.922301+05:30*;f;DECLARE
BEGIN
EXEC
16650;sampledb;13243;10;enterprisedb;Postgres Studio -
Browser;192.168.0.180;;3907;2012-12-26
16:35:45.753172+05:30;;2012-12-26 16:35:46.577723+05:30;f;IDLE

Also, tried to Kill it Firstly by using Cancel Backend and then Terminate
Backend output showing True but still remaining as a process (i.e. in
pg_stat_activity).




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Cursor-fetch-Problem-tp5737915p5737995.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Cursor loop - stop current iteration and continue with next iteration

[Dinesh Kumara]

Greetings..

Please advice me how to stop current iteration of cursor loop and
continue with next iteration .

...
contractlistquery = '';
OPEN rec_contractlist FOR EXECUTE contractlistquery;
 LOOP
 FETCH rec_contractlist INTO
contract_from_date,contract_to_date,contract_inv_type,contract_regionid;
 IF NOT FOUND THEN
 EXIT;
 END IF;

 IF contract_count = 0 THEN
  contract_initial_region = contract_regionid;
 END IF;

  IF contract_initial_region  contract_regionid THEN 
   [Need to stop current
iteration and continue with next iteration.]
   ELSE
  ...
  More logic s here...
  ...
  END IF;

 END LOOP;
CLOSE rec_contractlist;
...

Really appreciate if you can help me on this.
-- 

 Thanks and Regards,

Dinesh Kumara,

Software Engineer,

Reservation Gateway Inc,

Email:din...@rezgateway.com

www.rezgateway.com

Re: [GENERAL] Cursor loop - stop current iteration and continue with next iteration

[David Johnston]

On Dec 21, 2011, at 0:36, Dinesh Kumara din...@rezgateway.com wrote:

 Greetings..
 
 Please advice me how to stop current iteration of cursor loop and continue 
 with next iteration .
 
 ...
 contractlistquery = '';
 OPEN rec_contractlist FOR EXECUTE contractlistquery;
 LOOP
 FETCH rec_contractlist INTO  
 contract_from_date,contract_to_date,contract_inv_type,contract_regionid;
 IF NOT FOUND THEN
 EXIT;
 END IF;
 
 IF contract_count = 0 THEN
 contract_initial_region = contract_regionid;
 END IF;
 
 IF contract_initial_region  contract_regionid THEN 
 [Need to stop current iteration and continue with next iteration.]
ELSE
 ...
 More  logic s here...
 ...
 END IF;
 
 END LOOP;
 CLOSE rec_contractlist;
 ...
 
 Really appreciate if you can help me on this.
 -- 
 
 Thanks and Regards, 
 Dinesh Kumara, 
 Software Engineer, 
 Reservation Gateway Inc, 
 Email:din...@rezgateway.com 
 www.rezgateway.com 
 image002.png

See

39.6.3.3

David J.

[GENERAL] Cursor metadata

[Andy Chambers]

Hi All,

Is there anywhere in the postgres catalog where one can access metadata  
about a held cursor.  Type information

in particular would be really useful.

Cheers,
Andy

--
Andy Chambers

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor metadata

[Tom Lane]

Andy Chambers achamb...@mcna.net writes:
 Is there anywhere in the postgres catalog where one can access metadata  
 about a held cursor.

The pg_cursors system view offers some info ...

 Type information
 in particular would be really useful.

... but not that.  Usually the best way to get information about the
columns of a table/view/cursor is to fetch a row from it and use the
metadata that's provided by the fetch mechanism.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] cursor with dinamic string

[Luca Santaniello]

Hi all,

I need use dinamic string for my cursor... My code is:

query varchar := 'field1, field2 from ''' || tableName ||''' ; 
//has dinamic params


then I create cursor...

myCursor CURSOR FOR SELECT query;

I compile my function but when i run it I obtain error...

ERROR:  invalid input syntax for integer: ..
CONTEXT:  PL/pgSQL function my_function line 72 at FETCH

If I use static string, I don't have problems!!!

Can I solve it?

Thanks in advance

--
Luca Santaniello
email: luca.santaniello...@gmail.com
skype: luca.santaniello
msn: lukett...@hotmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cursor with dinamic string

[Pavel Stehule]

Hello

2011/3/25 Luca Santaniello luca.santaniello...@gmail.com:
 Hi all,

 I need use dinamic string for my cursor... My code is:

 query varchar := 'field1, field2 from ''' || tableName ||''' ; //has
 dinamic params


use a FOR EXECUTE statement

FOR r IN EXECUTE 'SELECT .. FROM ' || quote_ident(tableName) || ' '
LOOP
  ...

you can use OPEN FOR EXECUTE too, but FOR statement is preferable

Regards

Pavel Stehule

http://www.postgresql.org/docs/9.0/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING

 then I create cursor...

 myCursor CURSOR FOR SELECT query;

 I compile my function but when i run it I obtain error...

 ERROR:  invalid input syntax for integer: ..
 CONTEXT:  PL/pgSQL function my_function line 72 at FETCH

 If I use static string, I don't have problems!!!

 Can I solve it?

 Thanks in advance

 --
 Luca Santaniello
 email: luca.santaniello...@gmail.com
 skype: luca.santaniello
 msn: lukett...@hotmail.com


 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cursor with dinamic string

[Luca Santaniello]

Thank you very much


Hello

2011/3/25 Luca Santanielloluca.santaniello...@gmail.com:

Hi all,

I need use dinamic string for my cursor... My code is:

query varchar := 'field1, field2 from ''' || tableName ||''' ; //has
dinamic params


use a FOR EXECUTE statement

FOR r IN EXECUTE 'SELECT .. FROM ' || quote_ident(tableName) || ' '
LOOP
   ...

you can use OPEN FOR EXECUTE too, but FOR statement is preferable

Regards

Pavel Stehule

http://www.postgresql.org/docs/9.0/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING


then I create cursor...

myCursor CURSOR FOR SELECT query;

I compile my function but when i run it I obtain error...

ERROR:  invalid input syntax for integer: ..
CONTEXT:  PL/pgSQL function my_function line 72 at FETCH

If I use static string, I don't have problems!!!

Can I solve it?

Thanks in advance

--
Luca Santaniello
email: luca.santaniello...@gmail.com
skype: luca.santaniello
msn: lukett...@hotmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




--
Luca Santaniello
email: luca.santaniello...@gmail.com | luca.santanie...@email.it
phone: +39 3470373994
skype: luca.santaniello
msn: lukett...@hotmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] cursor MOVE vs OFFSET in SELECT

[silly8888]

Suppose that you have a query, say $sql_query, which is very
complicated and produces many rows. Which of the following is going to
be faser:

$sql_query OFFSET 3000 LIMIT 12;

or

BEGIN;
DECLARE cur1 CURSOR FOR $sql_query;
MOVE 3000 IN cur1;
FETCH 12 FROM cur1;
COMMIT;

Naturally, the former cannot be slower than the latter. So my question
essentially is whether the MOVE operation on a cursor is
(significantly) slower that a OFFSET on the SELECT.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cursor MOVE vs OFFSET in SELECT

[silly8888]

2009/10/26 Grzegorz Jaśkiewicz gryz...@gmail.com:


 On Mon, Oct 26, 2009 at 10:30 AM, silly silly8...@gmail.com wrote:

 Suppose that you have a query, say $sql_query, which is very
 complicated and produces many rows. Which of the following is going to
 be faser:

    $sql_query OFFSET 3000 LIMIT 12;

 or

    BEGIN;
    DECLARE cur1 CURSOR FOR $sql_query;
    MOVE 3000 IN cur1;
    FETCH 12 FROM cur1;
    COMMIT;

 Naturally, the former cannot be slower than the latter. So my question
 essentially is whether the MOVE operation on a cursor is
 (significantly) slower that a OFFSET on the SELECT.


 OFFSET/LIMIT. Afaik cursor always fetches everything.

Well, in my experiments they always perform the same. I suspect that
the way SELECT/OFFSET is implemented is not much different than
cursor/MOVE.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cursor MOVE vs OFFSET in SELECT

[Pavel Stehule]

2009/10/26 Grzegorz Jaśkiewicz gryz...@gmail.com:


 On Mon, Oct 26, 2009 at 10:30 AM, silly silly8...@gmail.com wrote:

 Suppose that you have a query, say $sql_query, which is very
 complicated and produces many rows. Which of the following is going to
 be faser:

    $sql_query OFFSET 3000 LIMIT 12;

 or

    BEGIN;
    DECLARE cur1 CURSOR FOR $sql_query;
    MOVE 3000 IN cur1;
    FETCH 12 FROM cur1;
    COMMIT;

 Naturally, the former cannot be slower than the latter. So my question
 essentially is whether the MOVE operation on a cursor is
 (significantly) slower that a OFFSET on the SELECT.


 OFFSET/LIMIT. Afaik cursor always fetches everything.

OFFSET/LIMIT process same rows as cursor. There could be only one
difference. Cursors uses strategy fast first row, SELECT uses
minimum complete query time.

Regards
Pavel Stehule



 --
 GJ


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cursor MOVE vs OFFSET in SELECT

[Alvaro Herrera]

silly escribió:
 2009/10/26 Grzegorz Jaśkiewicz gryz...@gmail.com:
 
 
  On Mon, Oct 26, 2009 at 10:30 AM, silly silly8...@gmail.com wrote:
 
  Suppose that you have a query, say $sql_query, which is very
  complicated and produces many rows. Which of the following is going to
  be faser:
 
     $sql_query OFFSET 3000 LIMIT 12;
 
  or
 
     BEGIN;
     DECLARE cur1 CURSOR FOR $sql_query;
     MOVE 3000 IN cur1;
     FETCH 12 FROM cur1;
     COMMIT;
 
  Naturally, the former cannot be slower than the latter. So my question
  essentially is whether the MOVE operation on a cursor is
  (significantly) slower that a OFFSET on the SELECT.
 
 
  OFFSET/LIMIT. Afaik cursor always fetches everything.
 
 Well, in my experiments they always perform the same. I suspect that
 the way SELECT/OFFSET is implemented is not much different than
 cursor/MOVE.

The cursor could choose a different plan due to the fast startup
behavior that Pavel alludes to.  You can actually change that by setting
the cursor_tuple_fraction parameter.  Whether this plan is faster or
slower than the other one is problem dependent.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cursor MOVE vs OFFSET in SELECT

[silly8888]

On Mon, Oct 26, 2009 at 10:28 AM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 silly escribió:
 2009/10/26 Grzegorz Jaśkiewicz gryz...@gmail.com:
 
 
  On Mon, Oct 26, 2009 at 10:30 AM, silly silly8...@gmail.com wrote:
 
  Suppose that you have a query, say $sql_query, which is very
  complicated and produces many rows. Which of the following is going to
  be faser:
 
     $sql_query OFFSET 3000 LIMIT 12;
 
  or
 
     BEGIN;
     DECLARE cur1 CURSOR FOR $sql_query;
     MOVE 3000 IN cur1;
     FETCH 12 FROM cur1;
     COMMIT;
 
  Naturally, the former cannot be slower than the latter. So my question
  essentially is whether the MOVE operation on a cursor is
  (significantly) slower that a OFFSET on the SELECT.
 
 
  OFFSET/LIMIT. Afaik cursor always fetches everything.

 Well, in my experiments they always perform the same. I suspect that
 the way SELECT/OFFSET is implemented is not much different than
 cursor/MOVE.

 The cursor could choose a different plan due to the fast startup
 behavior that Pavel alludes to.  You can actually change that by setting
 the cursor_tuple_fraction parameter.  Whether this plan is faster or
 slower than the other one is problem dependent.

 --
 Alvaro Herrera                                http://www.CommandPrompt.com/
 The PostgreSQL Company - Command Prompt, Inc.



OK, so based on what Alvaro  Pavel said, the following two
possibilities are equivalent as far as the query planner is concerned:

   $sql_query OFFSET 3000 LIMIT 12;

or

   BEGIN;
   SET LOCAL cursor_tuple_fraction=1;
   DECLARE cur1 CURSOR FOR $sql_query;
   MOVE 3000 IN cur1;
   FETCH 12 FROM cur1;
   COMMIT;

The problem is that in the latter case, the query planner doesn't know
in advance that we are going to skip the first 3000 rows.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] cursor question

[Andreas Wenk]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

short question. Why is this not possible:

CREATE OR REPLACE FUNCTION user_cursor_open(text) RETURNS refcursor AS $$
DECLARE
curs1 CURSOR FOR SELECT * FROM $1;
BEGIN
OPEN curs1;
RETURN curs1;
END; $$ LANGUAGE plpgsql;

Or, how do I set a parameter when using cursor?

Thanks in advance

- --
St.Pauli - Hamburg - Germany

Andreas Wenk


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJpWlyVa7znmSP9AwRAv2MAJ9jHICtLeIEv+QVzeqBWSkheXDmpgCgwMUH
t0/HWRPsx56jGhSVUQXyb9I=
=XMXi
-END PGP SIGNATURE-

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cursor question

[Pavel Stehule]

Hello

when you would to change name of table - you have to use dynamic query

38.7.2.2. OPEN FOR EXECUTE

OPEN unbound_cursor [ [ NO ] SCROLL ] FOR EXECUTE query_string;


http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html

regards
Pavel Stehule

2009/2/25 Andreas Wenk a.w...@netzmeister-st-pauli.de:
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1

 Hi,

 short question. Why is this not possible:

 CREATE OR REPLACE FUNCTION user_cursor_open(text) RETURNS refcursor AS $$
 DECLARE
        curs1 CURSOR FOR SELECT * FROM $1;
 BEGIN
        OPEN curs1;
        RETURN curs1;
 END; $$ LANGUAGE plpgsql;

 Or, how do I set a parameter when using cursor?

 Thanks in advance

 - --
 St.Pauli - Hamburg - Germany

 Andreas Wenk


 -BEGIN PGP SIGNATURE-
 Version: GnuPG v1.4.6 (GNU/Linux)
 Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

 iD8DBQFJpWlyVa7znmSP9AwRAv2MAJ9jHICtLeIEv+QVzeqBWSkheXDmpgCgwMUH
 t0/HWRPsx56jGhSVUQXyb9I=
 =XMXi
 -END PGP SIGNATURE-

 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cursor question

[Andreas Wenk]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Pavel Stehule schrieb:
 Hello
 
 when you would to change name of table - you have to use dynamic query
 
 38.7.2.2. OPEN FOR EXECUTE
 
 OPEN unbound_cursor [ [ NO ] SCROLL ] FOR EXECUTE query_string;
 
 
 http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html
 
 regards
 Pavel Stehule

Hi Pavel,

thanks a lot. It works for me like this:

CREATE OR REPLACE FUNCTION user_cursor_test(refcursor,text) RETURNS refcursor 
AS $$
BEGIN
OPEN $1 FOR EXECUTE 'SELECT name FROM ' || $2;
RETURN $1;
END; $$ LANGUAGE plpgsql;

Cheers

Andy


 2009/2/25 Andreas Wenk a.w...@netzmeister-st-pauli.de:
 Hi,
 
 short question. Why is this not possible:
 
 CREATE OR REPLACE FUNCTION user_cursor_open(text) RETURNS refcursor AS $$
 DECLARE
curs1 CURSOR FOR SELECT * FROM $1;
 BEGIN
OPEN curs1;
RETURN curs1;
 END; $$ LANGUAGE plpgsql;
 
 Or, how do I set a parameter when using cursor?
 
 Thanks in advance
 

- --
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJpYU2Va7znmSP9AwRAgGAAJ9En2TEupu7t994DIxi9ql5LH5sXACdHIi1
jNiaLX2mEmYWRxHDcGavbsM=
=XUcF
-END PGP SIGNATURE-

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor Error

[Craig Ringer]

Bob Pawley wrote:
 Is it allowed to declare a cursor in this manner??
 
 Declare
 procgraphic cursor for select p_id.p_id.process_id
 from p_id.p_id, processes_count
 where p_id.p_id.p_id_id = processes_count.p_id_id;

Using DECLARE instead of OPEN? Yes, but that won't somehow make a cursor
involving a join updatable. See:

http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html

DECLARE and OPEN do not have exactly the same meaning, as explained by
the above documentation. To use a cursor defined with DECLARE you must
use OPEN - see section 38.7.2.3 (Opening a Bound Cursor) of the
documentation.

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Cursor Error

[Bob Pawley]

Could somebody translate this error message for me??

Bob

cursor unnamed portal 1 is not simply updateable scan of table p_id

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor Error

[Tom Lane]

Bob Pawley [EMAIL PROTECTED] writes:
 Could somebody translate this error message for me??
 cursor unnamed portal 1 is not simply updateable scan of table p_id

You're trying to do an UPDATE WHERE CURRENT OF cursor, right?
What it means is that the cursor definition is too complicated for
Postgres to figure out which row to update.  Without seeing the
cursor definition it's hard to say more.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor Error

[Bob Pawley]

Right.

This is the cursor statement.

Open procgraphic for select p_id.p_id.process_id from p_id.p_id, 
processes_count

where p_id.p_id.p_id_id = processes_count.p_id_id;

If process_total = 1 Then

 Fetch first from procgraphic into process_id;

 Update p_id.p_id
 set proc_graphic_position = '1'
 where current of procgraphic;

I get the same error message when I define the cursor with the same select.

I am not sure how to make the query simpler and still have it access the 
right row on fetch.


Bob



- Original Message - 
From: Tom Lane [EMAIL PROTECTED]

To: Bob Pawley [EMAIL PROTECTED]
Cc: PostgreSQL pgsql-general@postgresql.org
Sent: Thursday, July 31, 2008 9:30 AM
Subject: Re: [GENERAL] Cursor Error



Bob Pawley [EMAIL PROTECTED] writes:

Could somebody translate this error message for me??
cursor unnamed portal 1 is not simply updateable scan of table p_id


You're trying to do an UPDATE WHERE CURRENT OF cursor, right?
What it means is that the cursor definition is too complicated for
Postgres to figure out which row to update.  Without seeing the
cursor definition it's hard to say more.

regards, tom lane 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor Error

[Tom Lane]

Bob Pawley [EMAIL PROTECTED] writes:
 Right.
 This is the cursor statement.

  Open procgraphic for select p_id.p_id.process_id from p_id.p_id, 
 processes_count
  where p_id.p_id.p_id_id = processes_count.p_id_id;

Sorry, we're not bright enough to handle WHERE CURRENT OF on a join
--- per the fine manual,

The cursor must be a simple (non-join, non-aggregate) query on
the UPDATE's target table.

I don't recall offhand whether there's some deep technical reason
for the restriction against joins, or we just didn't get around to
it.  In any case, you'll need to change the cursor to return the
table's primary key and use that to target the UPDATE.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor Error

[Bob Pawley]

Is it allowed to declare a cursor in this manner??

Declare
procgraphic cursor for select p_id.p_id.process_id from p_id.p_id, 
processes_count

  where p_id.p_id.p_id_id = processes_count.p_id_id;

Bob



- Original Message - 
From: Tom Lane [EMAIL PROTECTED]

To: Bob Pawley [EMAIL PROTECTED]
Cc: PostgreSQL pgsql-general@postgresql.org
Sent: Thursday, July 31, 2008 9:50 AM
Subject: Re: [GENERAL] Cursor Error



Bob Pawley [EMAIL PROTECTED] writes:

Right.
This is the cursor statement.



 Open procgraphic for select p_id.p_id.process_id from p_id.p_id,
processes_count
 where p_id.p_id.p_id_id = processes_count.p_id_id;


Sorry, we're not bright enough to handle WHERE CURRENT OF on a join
--- per the fine manual,

The cursor must be a simple (non-join, non-aggregate) query on
the UPDATE's target table.

I don't recall offhand whether there's some deep technical reason
for the restriction against joins, or we just didn't get around to
it.  In any case, you'll need to change the cursor to return the
table's primary key and use that to target the UPDATE.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Cursor

[Bob Pawley]

I have the following cursor that gives me an error near open.

Can someone please tell me what I am doing wrong??

Bob



 DECLARE

procgraphic cursor for select process_id from p_id.p_id,  processes_count 
   where p_id.p_id.p_id_id = processes_count.p_id_id;


begin

 Open procgraphic ;

Fetch first from procgraphic into process_id;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor

[Richard Huxton]

Bob Pawley wrote:

I have the following cursor that gives me an error near open.

Can someone please tell me what I am doing wrong??
 DECLARE
procgraphic cursor for select process_id from p_id.p_id,  
processes_countwhere p_id.p_id.p_id_id = processes_count.p_id_id;



begin

 Open procgraphic ;


There is no OPEN, you just FETCH


Fetch first from procgraphic into process_id;


--
  Richard Huxton
  Archonet Ltd

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor

[Tom Lane]

Richard Huxton [EMAIL PROTECTED] writes:
 Bob Pawley wrote:
 DECLARE
 procgraphic cursor for select process_id from p_id.p_id,  
 processes_countwhere p_id.p_id.p_id_id = processes_count.p_id_id;

 begin
 
 Open procgraphic ;

 There is no OPEN, you just FETCH

No, he does need an OPEN.  The extract looks correct as far as it goes,
so I think the mistake was in something that was omitted.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor

[Bob Pawley]

Following is more complete. The balance of the trigger that is not shown 
works when tested separately. I didn't include it because it is quite long.


Bob


DECLARE
process_total integer ;
process_id integer ;
procgraphic cursor for select process_id from p_id.p_id, processes_count
  where p_id.p_id.p_id_id = processes_count.p_id_id;

begin

Insert into processes_count (p_id_id)
select new.p_id_id from project.project ;

Select count (p_id.p_id.process_id) INTO process_total
  FROM p_id.p_id, processes_count
  Where p_id.p_id.p_id_id = processes_count.p_id_id;

  Open procgraphic;

Fetch first from procgraphic into process_id;

Update p_id.p_id
set proc_graphic_position = one
From graphics.proc_position, processes_count
where graphics.proc_position.proc_count = process_total
and process_id = p_id.p_id.process_id;





- Original Message - 
From: Tom Lane [EMAIL PROTECTED]

To: Richard Huxton [EMAIL PROTECTED]
Cc: Bob Pawley [EMAIL PROTECTED]; PostgreSQL 
pgsql-general@postgresql.org

Sent: Tuesday, July 29, 2008 2:35 PM
Subject: Re: [GENERAL] Cursor



Richard Huxton [EMAIL PROTECTED] writes:

Bob Pawley wrote:

DECLARE
procgraphic cursor for select process_id from p_id.p_id,
processes_countwhere p_id.p_id.p_id_id = processes_count.p_id_id;

begin

Open procgraphic ;



There is no OPEN, you just FETCH


No, he does need an OPEN.  The extract looks correct as far as it goes,
so I think the mistake was in something that was omitted.

regards, tom lane 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor

[Tom Lane]

Bob Pawley [EMAIL PROTECTED] writes:
 Following is more complete. The balance of the trigger that is not shown 
 works when tested separately. I didn't include it because it is quite long.

Hmm, I still don't see anything that looks like a syntax error, but
I'll bet this is a name collision rather than the effect you want:

  DECLARE
  process_total integer ;
  process_id integer ;
   ^^
  procgraphic cursor for select process_id from p_id.p_id, processes_count
 ^^
where p_id.p_id.p_id_id = processes_count.p_id_id;

You probably ought to qualify the column reference in the cursor.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor

[David Wilson]

On Tue, Jul 29, 2008 at 5:42 PM, Bob Pawley [EMAIL PROTECTED] wrote:

 begin

Don't you need a ; after your begin...?

-- 
- David T. Wilson
[EMAIL PROTECTED]

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor

[Adrian Klaver]

 -- Original message --
From: Tom Lane [EMAIL PROTECTED]
 Bob Pawley [EMAIL PROTECTED] writes:
  Following is more complete. The balance of the trigger that is not shown 
  works when tested separately. I didn't include it because it is quite long.
 
 Hmm, I still don't see anything that looks like a syntax error, but
 I'll bet this is a name collision rather than the effect you want:
 
   DECLARE
   process_total integer ;
   process_id integer ;
^^
   procgraphic cursor for select process_id from p_id.p_id, processes_count
  ^^
 where p_id.p_id.p_id_id = processes_count.p_id_id;
   ^^

Just to clarify is this supposed to be schema p_id,table p_id,column p_id_id?

 
 You probably ought to qualify the column reference in the cursor.
 
   regards, tom lane
 


--
Adrian Klaver
[EMAIL PROTECTED]


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor

[Bob Pawley]

Thanks Tom

Qualifying the column was the solution.

Bob


- Original Message - 
From: Tom Lane [EMAIL PROTECTED]

To: Bob Pawley [EMAIL PROTECTED]
Cc: Richard Huxton [EMAIL PROTECTED]; PostgreSQL 
pgsql-general@postgresql.org

Sent: Tuesday, July 29, 2008 2:51 PM
Subject: Re: [GENERAL] Cursor



Bob Pawley [EMAIL PROTECTED] writes:

Following is more complete. The balance of the trigger that is not shown
works when tested separately. I didn't include it because it is quite 
long.


Hmm, I still don't see anything that looks like a syntax error, but
I'll bet this is a name collision rather than the effect you want:


 DECLARE
 process_total integer ;
 process_id integer ;

  ^^

 procgraphic cursor for select process_id from p_id.p_id, processes_count

^^

   where p_id.p_id.p_id_id = processes_count.p_id_id;


You probably ought to qualify the column reference in the cursor.

regards, tom lane 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor

[Bob Pawley]

Yes

Bob

- Original Message - 
From: Adrian Klaver [EMAIL PROTECTED]

To: Tom Lane [EMAIL PROTECTED]; Bob Pawley [EMAIL PROTECTED]
Cc: Richard Huxton [EMAIL PROTECTED]; PostgreSQL 
pgsql-general@postgresql.org

Sent: Tuesday, July 29, 2008 3:03 PM
Subject: Re: [GENERAL] Cursor



-- Original message --
From: Tom Lane [EMAIL PROTECTED]

Bob Pawley [EMAIL PROTECTED] writes:
 Following is more complete. The balance of the trigger that is not 
 shown
 works when tested separately. I didn't include it because it is quite 
 long.


Hmm, I still don't see anything that looks like a syntax error, but
I'll bet this is a name collision rather than the effect you want:

  DECLARE
  process_total integer ;
  process_id integer ;
   ^^
  procgraphic cursor for select process_id from p_id.p_id, 
 processes_count

 ^^
where p_id.p_id.p_id_id = processes_count.p_id_id;

  ^^

Just to clarify is this supposed to be schema p_id,table p_id,column 
p_id_id?




You probably ought to qualify the column reference in the cursor.

regards, tom lane




--
Adrian Klaver
[EMAIL PROTECTED]




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor

[Tom Lane]

Bob Pawley [EMAIL PROTECTED] writes:
 Qualifying the column was the solution.

Huh.  What was the error message you got, exactly?  Because it doesn't
seem like that should have led to a syntax error.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor

[Bob Pawley]

The syntax error was running the function while not in a trigger.

The trigger gave null as a return.

The error was syntax error at or near Open.

Bob


- Original Message - 
From: Tom Lane [EMAIL PROTECTED]

To: Bob Pawley [EMAIL PROTECTED]
Cc: Richard Huxton [EMAIL PROTECTED]; PostgreSQL 
pgsql-general@postgresql.org

Sent: Tuesday, July 29, 2008 3:30 PM
Subject: Re: [GENERAL] Cursor



Bob Pawley [EMAIL PROTECTED] writes:

Qualifying the column was the solution.


Huh.  What was the error message you got, exactly?  Because it doesn't
seem like that should have led to a syntax error.

regards, tom lane 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor

[Christophe]

On Jul 29, 2008, at 2:35 PM, Tom Lane wrote:

No, he does need an OPEN.


Really?  I thought that PG didn't use OPEN:

The PostgreSQL server does not implement an OPEN statement for  
cursors; a cursor is considered to be open when it is declared.


http://www.postgresql.org/docs/8.3/interactive/sql-declare.html

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor

[Klint Gore]

Christophe wrote:

On Jul 29, 2008, at 2:35 PM, Tom Lane wrote:
 No, he does need an OPEN.

Really?  I thought that PG didn't use OPEN:

The PostgreSQL server does not implement an OPEN statement for  
cursors; a cursor is considered to be open when it is declared.


http://www.postgresql.org/docs/8.3/interactive/sql-declare.html
  

It's different in PL/pgSQL.

Before a cursor can be used to retrieve rows, it must be opened. (This 
is the equivalent action to the SQL command DECLARE CURSOR.)


http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html


klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  
Fax: 02 6773 3266

EMail: [EMAIL PROTECTED]


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cursor

[Christophe]

On Jul 29, 2008, at 4:51 PM, Klint Gore wrote:

It's different in PL/pgSQL.


Ah, yes, sorry, didn't catch that it was a PL/pgSQL function.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] cursor

[Bob Pawley]

I understand that a cursor can be opened and used through sql commands.

However, when a host application accesses a particular row on a particular 
table can that be detected within the sql medium?


Or does that information have the be accessed by transmitting from the host?

Bob 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cursor manipulation

[Albe Laurenz]

Justin wrote:
 Cursor manipulation with select statements
 
 This is something i did in foxpro and wonder if it is at all
 possible in pl/pgSQL.

Yes, it is. See
http://www.postgresql.org/docs/current/static/plpgsql-cursors.html

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] cursor manipulation

[Justin]

Cursor manipulation with select statements

This is something i did in foxpro and wonder if it is at all possible in 
pl/pgSQL.  I would create a cursor in foxpro then populate it with 
Inserts then after all that was done use a Select statement to do more 
manipulation if need be


the Foxpro code would look something like this
---
Create Cursor plaything (cname char(10),  price n(14,4),  qty n(14,4))

For  i in SomeArrayofObjects
   insert into playting (i.Company, i.Price, i.qty)
end for

Select cName,  max(price*qty), avg(price * qty), min(price*avg)  from 
plaything  group by Cname into cursor plaything2



After a cursor has been created can a Select statement see it or do i 
have to create a temp table

thanks

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] cursor vs. for _row in select...

[Ivan Sergio Borgonovo]

Can somebody help me to appreciate the difference between returning a
setof record with cursor or with for _row in select ... as in

http://people.planetpostgresql.org/xzilla/index.php?/archives/149-out-parameter-sql-plpgsql-examples.html

Once I use and define cursors read only, no scroll, insensitive (that
should give enough information to use the cursor as a standard query)
I see there is an interesting advantage over for _row in select: you
don't have to specify the output type.

Is there any hidden penalty using cursor vs. for _row in select?

What's the meaning of:
which can be used to retrieve a small number of rows at a time out
of a larger query.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Cursor Contents display in pgAdmin - Help.

[steve shiflett]

I wish to display the content of a cursor but haven't discovered the 
trick.  Here's what I'm doing:


-The function


CREATE OR REPLACE FUNCTION tmp.sps(character varying, date)
RETURNS refcursor AS
$BODY$
DECLARE
ref refcursor;
BEGIN
  OPEN ref FOR select * from tmp.sps_measurement  where logdate  $2;
  RETURN  ref;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

---psql 
script expected to display the contents of the cursor


BEGIN;

declare
ref cursor for select tmp.sps('=','2006-06-12') ;

FETCH all IN ref;
commit;

--- Output:

xxx z=#  \i sps_test.sql
BEGIN
DECLARE CURSOR
  sps


unnamed portal 9
(1 row)

COMMIT
xxx z=#

(I am expecting the rows in the tmp.sps_measurement table.)  Can anyone 
help me out with this?


Steve

[GENERAL] Cursor timeout in postgres

[Tim Tassonis]

Hi all

When examining strange behaviour in one of my programs I found out that 
I must have somehow gotten into a timeout situation when fetching rows 
from a cursor. My program read the first row, did some stuff for six 
minutes and then tried to fetch the second row, which failed. The 
connection however was still alive and further database access in the 
program worked m'kay.


While I think the responsible component made a good decision to timeout 
after 5 minutes of inactivity, I still wonder who bit me.



My program was written in php, using the odbc interface and the odbc 
driver from postgres compiled against 8.1.5 libpg, accessing a 8.1.5 
database (all on the same machine, on linux 32bit). I have not perfomed 
any relevant tweaking in the config files (apart from php.ini).


Has anybody got the quick answer?

Tim

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

Re: [GENERAL] Cursor timeout in postgres

[Tom Lane]

Tim Tassonis [EMAIL PROTECTED] writes:
 When examining strange behaviour in one of my programs I found out that 
 I must have somehow gotten into a timeout situation when fetching rows 
 from a cursor. My program read the first row, did some stuff for six 
 minutes and then tried to fetch the second row, which failed. The 
 connection however was still alive and further database access in the 
 program worked m'kay.

Failed how, exactly?  Did anything show up in the postmaster log?

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Cursor timeout in postgres

[Tim Tassonis]

Hi Tom

Tom Lane wrote:

Tim Tassonis [EMAIL PROTECTED] writes:
When examining strange behaviour in one of my programs I found out that 
I must have somehow gotten into a timeout situation when fetching rows 
from a cursor. My program read the first row, did some stuff for six 
minutes and then tried to fetch the second row, which failed. The 
connection however was still alive and further database access in the 
program worked m'kay.


Failed how, exactly?  Did anything show up in the postmaster log?


Forgot to check that, sorry. From my program's point of view, it failed 
silently. When an odbc_exec() succeeds, one generally just loops over 
the rows by odbc_fetch_row(), until it fails. The fetch might have 
returned a message, but this was not checked, as odbc_fetch_row() is 
bound to fail anyway when no more rows are present.


I try to reproduce the situation tomorrow and will also check on any 
odbc_errmsg() messages and the postmaster log.


Bye
Tim


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Cursor timeout in postgres

[Tom Lane]

Tim Tassonis [EMAIL PROTECTED] writes:
 I try to reproduce the situation tomorrow and will also check on any 
 odbc_errmsg() messages and the postmaster log.

OK.  A couple of comments: the only timeout within Postgres itself is
statement_timeout, which I think wouldn't apply to your situation even
if you had it set (but I might be wrong).  We have also heard reports
of firewalls dropping idle connections, but in that scenario you'd
not still have a working connection afterwards.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Cursor body?

[Ashish Karalkar]

Hello all, 
I am trying to get the list of all foreign key tables
for a given primary table after getting this list I
want to filter out the name of table for which a
particular Primary table.primary key exsits in the
foreign table.foreign key column.
For that I have written a SP but the first cursor
which gives me list of Foreign key table is not
getting iterated. The SP  is working fine for only
first table returned by the cursor.
In short what i can point out is cursor is not
fetching  next rows.
The Sp is attached with this. Please help me.

CREATE OR REPLACE FUNCTION
sp_gen_foreign_keys_tables(out par_childtables text,
IN par_colname character varying , IN par_colvalue
integer) AS
$BODY$

 DECLARE
   curforeign refcursor ;
   curforeign1 refcursor;
   tablename text;
   columnname text;
   var_str1 text;
   var_str2 text;
   countno integer;
BEGIN   
var_str1:='select distinct (fk_relation),fk_column
from foreign_keys where pk_relation in (select
pk_relation from foreign_keys where pk_column='''||
par_colname||''')';

open curforeign for execute var_str1;
FETCH  curforeign  into tablename,columnname ;
var_str2:='select count(*) from '|| tablename || '
where ' || columnname ||' = '|| par_colvalue;
open curforeign1 for execute var_str2;
FETCH  curforeign1 into countno;
close curforeign1;
if countno  0 then
par_childtables:=par_childtables + ',' + tablename;
end if ;

close curforeign ;   

 

END; 
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

What i want is to iterate through the block from
FETCH  curforeign   to end if


also insted of using second cursor to get the count i
used select into (variable) but it gives me error 
Execute of select into is not implemented yet
I am using postgreSQL 8.2.

Thanks in advance

With Regards
Ashish Karalkar








 

Never Miss an Email
Stay connected with Yahoo! Mail on your mobile.  Get started!
http://mobile.yahoo.com/services?promote=mail

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] Cursor declaration

[Nigel Horne]

How do I declare a cursor in a stored procedure if that cursor contains
a WHERE which depends on the argument given to that stored procedure?

I can't get passed this message:

'Cannot mix placeholder styles $1 and :foo'

The message doesn't give a useful line number, so I don't even know
which
line it's complaining about.

-Nigel


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

Re: [GENERAL] Cursor declaration

[Richard Huxton]

Nigel Horne wrote:

How do I declare a cursor in a stored procedure if that cursor contains
a WHERE which depends on the argument given to that stored procedure?

I can't get passed this message:

'Cannot mix placeholder styles $1 and :foo'

The message doesn't give a useful line number, so I don't even know
which
line it's complaining about.


Difficult to say - perhaps the source of the function would help. You 
don't actually mention what language you're using even.


However, I'd look for anywhere you're using :varname since that isn't 
valid plpgsql.


Then, I'd look at the OPEN ... CURSOR ... EXECUTE form of cursor opening 
 (see manuals for details).

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Cursor declaration

[Tom Lane]

Nigel Horne [EMAIL PROTECTED] writes:
 I can't get passed this message:
 'Cannot mix placeholder styles $1 and :foo'

There's no such message anywhere in the Postgres sources.  I suppose it
must be coming from whatever client-side library you are using (which
you didn't say).

regards, tom lane

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

Re: [GENERAL] Cursor Issue??

[DracKewl]

Thanks Roman for sticking with me on this!
For whatever reason I cannot load another langage, I think it has to do
with recompiling the program and installing all the options.  Not sure
though??  LANGUAGE plpgsql doesn't exist for me.

I still find this cursor limitation wacked.  I find it hard to believe
that nobody else is requiring this curosr funcionality.  Why else have
a cursor?
See the bottom from this link
http://www.postgresql.org/docs/7/interactive/sql-fetch.htm  Even in
there is display of a teaser that it can be done.  Too bad there is no
example.

My objective is to look at each record one at at time from top to
bottom.  I need to take that information in variable form, and run it
through a routine that is in the cursor block, then the end result
needs to end up in another table.  There will be times where I will
also need to scroll forward and backward.

I do understand, even though I don't agree with PostgreSQl approach to
this, that my only alternative is to create a function.  But as you
know getting a function to work in the way I need it to will require
custom functions.  What a pain!


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Cursor Issue??

[DracKewl]

Hey Roman,

Thanks for your reponse's!

I made it happen in MicrosoftSQL using the first code below.  The only
difference is I had to create variables.  Which I'm having a hard time
trying to replicate it in psql.

__Microsoft Code___
USE test
GO
DECLARE @qty INT, @Length varchar(20), @Width varchar(40)
DECLARE cursor1 SCROLL CURSOR FOR
SELECT * from Parts
OPEN cursor1
  FETCH FIRST FROM cursor1
  INTO @qty, @Length, @Width
  INSERT INTO PartsTemp (qty, Length, Width)
VALUES (@qty, @Length, @Width)
CLOSE cursor1
DEALLOCATE cursor1
GO


__psql Code___
(These declaration of vaiables don't work)
DECLARE c_qty INT;
DECLARE c_Length FLOAT;
DECLARE c_Width FLOAT;

BEGIN;
DECLARE cursor1 CURSOR FOR SELECT * FROM Parts;
FETCH FIRST FROM cursor1 INTO c_qty, c_Length, c_Width;
INSERT INTO partstemp VALUES (c_qty, c_Length, c_Width);
CLOSE cursor1;
COMMIT;

Got any ideas using variable to transfer singular rows?


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

Re: [GENERAL] Cursor Issue??

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2005-08-04 09:56:03 -0700:
 Thanks Roman for sticking with me on this!
 For whatever reason I cannot load another langage, I think it has to do
 with recompiling the program and installing all the options.  Not sure
 though??  LANGUAGE plpgsql doesn't exist for me.

Please send the output of this command:

createlang -U postgres -d $dbname plpgsql

 I still find this cursor limitation wacked.  I find it hard to believe
 that nobody else is requiring this curosr funcionality.  Why else have
 a cursor?

You are trying to use a cursor in interactive SQL. SQL99 doesn't
allow that at all!

 See the bottom from this link
 http://www.postgresql.org/docs/7/interactive/sql-fetch.htm  Even in
 there is display of a teaser that it can be done.  Too bad there is no
 example.

It says:

: Compatibility
: SQL92
:
: Note: The non-embedded use of cursors is a Postgres extension.
: The syntax and usage of cursors is being compared against the
: embedded form of cursors defined in SQL92. 
:
: SQL92 allows absolute positioning of the cursor for FETCH, and
: allows placing the results into explicit variables.
:
: FETCH ABSOLUTE #
:  FROM cursor
:  INTO :variable [, ...]
 
The text above is valid for SQL:1999 as well.

You fall in the non-embedded category, IOW that insufficient
functionality you are trying to use is outside the SQL standard.

The example servers to document the paragraph that begins SQL92
allows (...), IOW what you see is the SQL92, Embedded SQL syntax,
not what you can do in PostgreSQL.
 
 My objective is to look at each record one at at time from top to
 bottom.  I need to take that information in variable form, and run it
 through a routine that is in the cursor block, then the end result
 needs to end up in another table.  There will be times where I will
 also need to scroll forward and backward.
 
To me the fact that the interactive FETCH can only *display* the
row, while PL/PGSQL is always NO SCROLL and you can only FETCH NEXT
on it, looks like a bad combination of features and shortcomings.

Maybe you'd like to take this to the pgsql-docs@ list? At least
the more knowledgable people could add some real life (= nontrivial)
examples.

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Cursor Issue??

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2005-08-02 10:01:34 -0400:
 I made it happen in MicrosoftSQL using the first code below.  The only 
 difference is I had to create variables.  Which I'm having a hard time 
 trying to replicate it in psql.
 
 __Microsoft Code___
 USE test
 GO
 DECLARE @qty INT, @Length varchar(20), @Width varchar(40)
 DECLARE cursor1 SCROLL CURSOR FOR
 SELECT * from Parts
 OPEN cursor1
  FETCH FIRST FROM cursor1
  INTO @qty, @Length, @Width
  INSERT INTO PartsTemp (qty, Length, Width)
   VALUES (@qty, @Length, @Width)
 CLOSE cursor1
 DEALLOCATE cursor1
 GO

The code above puts a single, randomly chosen tuple from Parts into
PartsTemp. If that's all you need, you can do it with:

INSERT INTO PartsTemp (SELECT * FROM Parts LIMIT 1);

 __psql Code___
 (These declaration of vaiables don't work)
 DECLARE c_qty INT;
 DECLARE c_Length FLOAT;
 DECLARE c_Width FLOAT;
 
 BEGIN;
 DECLARE cursor1 CURSOR FOR SELECT * FROM Parts;
 FETCH FIRST FROM cursor1 INTO c_qty, c_Length, c_Width;
 INSERT INTO partstemp VALUES (c_qty, c_Length, c_Width);
 CLOSE cursor1;
 COMMIT;
 
 Got any ideas using variable to transfer singular rows?

If you need to do more (you aren't telling much), and want/need to
use cursors, you'll have to resort to using PL/pgSQL. This hack
would do it:

CREATE TABLE t1 (t1i INT, t1c CHAR(1));
CREATE TABLE t2 (t2i INT, t2c CHAR(1));

INSERT INTO t1 VALUES (1, 'a');
INSERT INTO t1 VALUES (2, 'b');
INSERT INTO t1 VALUES (3, 'c');

CREATE FUNCTION do_it()
RETURNS BOOLEAN
VOLATILE
LANGUAGE plpgsql
AS '
  DECLARE
_ti INTEGER;
_tc CHAR(1);
_c1 CURSOR FOR SELECT t1i, t1c FROM t1;
  BEGIN
OPEN _c1;
FETCH _c1 INTO _ti, _tc;
INSERT INTO t2 VALUES (_ti, _tc);
CLOSE _c1;
RETURN TRUE;
  END;
';

SELECT do_it();
DROP FUNCTION do_it();


But watch out, because PL/pgSQL doesn't provide a way to create
SCROLLable cursors, FETCH more than one tuple at a time, or FETCH
orientation.

I urge you to read about functions and PL/pgSQL in the manual:

http://www.postgresql.org/docs/current/static/server-programming.html
http://www.postgresql.org/docs/current/static/plpgsql.html
http://www.postgresql.org/docs/current/static/sql-createfunction.html

and note that SQL DECLARE is a different beast from PL/pgSQL DECLARE,
etc for other statements.

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Cursor Issue??

[DracKewl]

I found using the shell works but using the phAdminIII GUI is the one
that gives me problems.  I've even tried running it on EMS PostgreSQL
Manager 3.  Same results.  Is this normal?

Got a couple more questions regarding cursors.
1. When I try to run this statement (Declare curs1 refcursor;)  I get
an error ERROR:  syntax error at or near refcursor at character 23
2. Using Fetch count; also barfs on me.
3.  After I use Fetch First how do I take the contents and insert them
into another table?
  This is my guess.
  FETCH FIRST IN cursor1 INTO partstemp(qty, length, width)

BEGIN;
DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
FETCH FIRST IN cursor1 INTO partstemp(qty, length, width); My
guess but doesnt work.
CLOSE cursor1; 
COMMIT;


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Cursor Issue??

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2005-07-27 12:21:34 -0700:
 I found using the shell works but using the phAdminIII GUI is the one
 that gives me problems.  I've even tried running it on EMS PostgreSQL
 Manager 3.  Same results.  Is this normal?
 
 Got a couple more questions regarding cursors.
 1. When I try to run this statement (Declare curs1 refcursor;)  I get
 an error ERROR:  syntax error at or near refcursor at character 23

Have you seen the answer to this I sent you in my reply to your
offlist email? If not, reread the mail. If yes, and you still don't
see the problem: which part of

DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

does allow for DECLARE curs1 refcursor? The answer is simple:
none. You're trying to use a plpgsql declaration outside plpgsql.

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Cursor Issue??

[DracKewl]

BEGIN WORK;
DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
FETCH FIRST FROM cursor1;
CLOSE cursor1;
COMMIT WORK;


Query result with 1 rows discarded.
Query returned successfully with no result in 31 ms.

In the data output view nothing is returned?


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Cursor Issue??

[DracKewl]

Here is a further test:

--
BEGIN WORK;
DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
FETCH FIRST IN cursor1;
INSERT INTO partstemp VALUES (PARTS.QTY, PARTS.LENGTH, PARTS.WIDTH);
CLOSE cursor1;
COMMIT WORK;
--

What I get is the whole table put into partstemp.  I just wanted the
one row???
One more question what is the difference between using the term IN vs.
FROM?


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Cursor Issue??

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2005-07-26 14:06:34 -0700:
 BEGIN WORK;
 DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
 FETCH FIRST FROM cursor1;
 CLOSE cursor1;
 COMMIT WORK;
 
 
 Query result with 1 rows discarded.
 Query returned successfully with no result in 31 ms.
 
 In the data output view nothing is returned?

Complain to your 'data output view' vendor.

test=# create table parts (id serial, t text);
CREATE TABLE
test=# insert into parts (t) values ('aaa');
INSERT 72423 1
test=# insert into parts (t) values ('bbb');
INSERT 72424 1
test=# insert into parts (t) values ('ccc');
INSERT 72425 1
test=# select * from parts;
 id |  t
+-
  1 | aaa
  2 | bbb
  3 | ccc
(3 rows)

test=# BEGIN WORK;
BEGIN
test=# DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
DECLARE CURSOR
test=# FETCH FIRST FROM cursor1;
 id |  t
+-
  1 | aaa
(1 row)

test=# CLOSE cursor1;
CLOSE CURSOR
test=# COMMIT WORK;
COMMIT
test=#

As you can see, the fetched row is displayed just fine.

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Cursor not getting all rows

[Joseph Shraibman]

I'm running:
PostgreSQL 7.4.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 
20030222 (Red Hat Linux 3.2.2-5)

I do this:
BEGIN;
SELECT count(*) FROM u, d WHERE u.id = d.id AND ... ;
DECLARE cname CURSOR FOR SELECT u.field, d.field FROM u, d WHERE u.id = 
d.id AND ... ;

At the end of the fetching if the number of fetched does not equal the 
number from the SELECT count(*) I print out a warning message.  It 
happens every once in a while.  Today it happened four times.

1) missed 1 out of 703773
2) missed 3 out of 703765
3) missed 10 out of 703743
4) missed 12 out of 703660
How is this possible?  Because they are in the same transaction the 
count and the cursor should see the exact same data, right?

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] Cursor not getting all rows

[Scott Marlowe]

On Tue, 2005-05-17 at 12:19, Joseph Shraibman wrote:
 I'm running:
 
 PostgreSQL 7.4.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 
 20030222 (Red Hat Linux 3.2.2-5)
 
 
 I do this:
 
 BEGIN;
 SELECT count(*) FROM u, d WHERE u.id = d.id AND ... ;
 DECLARE cname CURSOR FOR SELECT u.field, d.field FROM u, d WHERE u.id = 
 d.id AND ... ;
 
 At the end of the fetching if the number of fetched does not equal the 
 number from the SELECT count(*) I print out a warning message.  It 
 happens every once in a while.  Today it happened four times.
 
 1) missed 1 out of 703773
 2) missed 3 out of 703765
 3) missed 10 out of 703743
 4) missed 12 out of 703660
 
 How is this possible?  Because they are in the same transaction the 
 count and the cursor should see the exact same data, right?

Only if you set transaction isolation to serializable.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Cursor not getting all rows

[Joseph Shraibman]

Scott Marlowe wrote:
Only if you set transaction isolation to serializable.
So am I getting data that was updated up until the time of the FETCH or 
the DECLARE CURSOR?

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] Cursor not getting all rows

[Tom Lane]

Joseph Shraibman jks@selectacast.net writes:
 I do this:

 BEGIN;
 SELECT count(*) FROM u, d WHERE u.id = d.id AND ... ;
 DECLARE cname CURSOR FOR SELECT u.field, d.field FROM u, d WHERE u.id = 
 d.id AND ... ;

 At the end of the fetching if the number of fetched does not equal the 
 number from the SELECT count(*) I print out a warning message.  It 
 happens every once in a while.  Today it happened four times.

 How is this possible?  Because they are in the same transaction the 
 count and the cursor should see the exact same data, right?

Not unless you are using SERIALIZABLE mode --- otherwise the cursor will
see whatever changes were committed during execution of the first SELECT.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Cursor not getting all rows

[Scott Marlowe]

On Tue, 2005-05-17 at 12:49, Joseph Shraibman wrote:
 Scott Marlowe wrote:
 
  Only if you set transaction isolation to serializable.
 
 So am I getting data that was updated up until the time of the FETCH or 
 the DECLARE CURSOR?

The data shouldn't change between the declare and the fetch, it's
between the count(*) and the declare that it can change.

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] Cursor problem

[Vidya]

Hello All,
How do I create a cursor in psql .

I have a function which opens a cursor .
SampleDB=# create function reffunc(refcursor) returns refcursor as $$SampleDB$# beginSampleDB$# open $1 for select col from test;SampleDB$# return $1;SampleDB$# end;SampleDB$# $$ language sql;ERROR: syntax error at or near "open" at character 67LINE 3: open $1 for select col from test;

I am not able to create a cursor , it errors out at 'open '


I tried a still more simple one 
 ^SampleDB=# create or replace function test(refcursor) returns refcursor as $$
SampleDB=#beginSampleDB$# syscur refcursor;
SampleDB=#end;SampleDB$# $$ language sql;ERROR: syntax error at or near "syscur" at character 68LINE 2: syscur refcursor;

How do I create a cursor .

Any help asap ?
Thanks
Vidya 
		Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site! 

Re: [GENERAL] Cursor problem

[Michael Fuhr]

On Fri, May 06, 2005 at 05:58:11AM -0700, Vidya wrote:

 How do I create a cursor in psql .
  
 I have a function which opens a cursor .
 SampleDB=# create function reffunc(refcursor) returns refcursor as $$
 SampleDB$# begin
 SampleDB$# open $1 for select col from test;
 SampleDB$# return $1;
 SampleDB$# end;
 SampleDB$# $$ language sql;
 ERROR:  syntax error at or near open at character 67
 LINE 3: open $1 for select col from test;

In this example the function's language should be plpgsql, not sql.
For more information about returning cursors from a PL/pgSQL function,
see the documentation:

http://www.postgresql.org/docs/8.0/interactive/plpgsql-cursors.html

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] cursor already in use error

[Michael Fuhr]

On Wed, Mar 02, 2005 at 09:21:44AM +0200, Sim Zacks wrote:

 PostGreSQL 8.0beta1

That's pretty old.  Have you tried 8.0.1?

 I have a function that uses a cursor and it is giving me the error: cursor
 crsr already in use when the parameters I pass in come from another table.
 
 The function works fine when I call it by itself, such as select
 PartNeedsReschedule(100,1) or select * from PartNeedsReschedule(100,1)
 but when I try select PartNeedsReschedule(PartID,1) from Parts then I get
 the error.

Could you post a simple, self-contained example that demonstrates
the problem?  Please show definitions of Parts and PartNeedsReschedule
and some sample data -- enough that somebody could copy what you
post into a test database and duplicate the problem.  It'll be
easier to help if we can see exactly what you're doing.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] cursor already in use error

[Sim Zacks]

I haven't tried 8.0.1 yet. I am planning on making the upgrade soon, but
since I haven't seen this issue discussed I did not think it had been fixed
just because I am using an older version. I did search the Internet and
found one person who had the same issue and found a workaround that won't
apply to my situation.
Here is an example of my problem:

create table test( TestID int primary key, name varchar);
insert into test(TestID,name) values(1,'Bob');
insert into test(testid,name) values(2,'Mark');
insert into test(testid,name) values(3,'Tom');

create table test2(Test2ID serial primary key, TestID int, comments
varchar);
insert into test2(TestID,Comments) values(1,'Comment 1');
insert into test2(TestID,Comments) values(1,'Comment 2');
insert into test2(TestID,Comments) values(1,'Comment 3');
insert into test2(TestID,Comments) values(2,'Comment 1');

create or replace function testcursor(thistestid int) returns varchar as
$$
declare
 crs Cursor for select comments from test a join test2 b on
a.testid=b.testid where a.TestID=thistestid;
 thiscomment varchar;
 totalstr varchar;
begin
 open crs;
 fetch crs into thiscomment;
 totalstr:='';
 while found loop
  totalstr:= totalstr || '-' || thiscomment;
  fetch crs into thiscomment;
 end loop;
 return totalstr;
end;
$$language 'plpgsql';

select name,testcursor(testid) from test; --doesn't work
select name,testcursor(testid) from test where testid=1; -- works (as does
testid=2 or 3)

Obviously in this example I could use a for loop which would remove the
problem, but in my real life complicated example, I cannot use a for loop
and I am stuck using a cursor. My real life example uses the cursor inside
of a for loop such that it opens the cursor before the loop starts and then
based on criteria inside the loop sometimes it reads the values and fetches
the next value and sometimes it doesn't.

Thank You
Sim

Michael Fuhr [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
 On Wed, Mar 02, 2005 at 09:21:44AM +0200, Sim Zacks wrote:

  PostGreSQL 8.0beta1

 That's pretty old.  Have you tried 8.0.1?

  I have a function that uses a cursor and it is giving me the error:
cursor
  crsr already in use when the parameters I pass in come from another
table.
 
  The function works fine when I call it by itself, such as select
  PartNeedsReschedule(100,1) or select * from PartNeedsReschedule(100,1)
  but when I try select PartNeedsReschedule(PartID,1) from Parts then I
get
  the error.

 Could you post a simple, self-contained example that demonstrates
 the problem?  Please show definitions of Parts and PartNeedsReschedule
 and some sample data -- enough that somebody could copy what you
 post into a test database and duplicate the problem.  It'll be
 easier to help if we can see exactly what you're doing.

 -- 
 Michael Fuhr
 http://www.fuhr.org/~mfuhr/

 ---(end of broadcast)---
 TIP 3: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly




---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] cursor already in use error

[Alban Hertroys]

Sim Zacks wrote:
create or replace function testcursor(thistestid int) returns varchar as
$$
declare
 crs Cursor for select comments from test a join test2 b on
a.testid=b.testid where a.TestID=thistestid;
 thiscomment varchar;
 totalstr varchar;
begin
 open crs;
 fetch crs into thiscomment;
 totalstr:='';
 while found loop
  totalstr:= totalstr || '-' || thiscomment;
  fetch crs into thiscomment;
 end loop;
close crs;
 return totalstr;
end;
$$language 'plpgsql';
select name,testcursor(testid) from test; --doesn't work
select name,testcursor(testid) from test where testid=1; -- works (as does
testid=2 or 3)
The second query works because you fetch only one record; You don't call 
the SP a second time with the cursor still open, while you do with the 
first query.

Always close your cursors.
--
Alban Hertroys
MAG Productions
T: +31(0)53 4346874
F: +31(0)53 4346876
E: [EMAIL PROTECTED]
W: http://www.magproductions.nl
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] cursor already in use error

[Sim Zacks]

 If I add close crs; before the function returns, I get this:

 SELECT name, testcursor(testid) FROM test;
  name |   testcursor
 --+
  Bob  | -Comment 1-Comment 2-Comment 3
  Mark | -Comment 1
  Tom  |
 (3 rows)


You got it.
I was closing the cursor at the end of the function, but there were other
possible returns in the middle.
I changed the code to add close before each return and it works like a
charm.

I rushed together the example, or I should have caught that.

Much thanks.
Sim




---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] cursor already in use error

[Sim Zacks]

 Always close your cursors.

Thanks.
In my real example I had multiple return points and did not close the cursor
before each one.



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] cursor already in use error

[Michael Fuhr]

On Wed, Mar 02, 2005 at 11:15:52AM +0200, Sim Zacks wrote:

 select name,testcursor(testid) from test; --doesn't work
 select name,testcursor(testid) from test where testid=1; -- works (as does
 testid=2 or 3)

If I add close crs; before the function returns, I get this:

SELECT name, testcursor(testid) FROM test;
 name |   testcursor   
--+
 Bob  | -Comment 1-Comment 2-Comment 3
 Mark | -Comment 1
 Tom  | 
(3 rows)

Is that what you're after?  If so, then the problem might simply
be that you're not closing the cursor when you're done with it.
Will closing it work in the real code?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] cursor already in use error

[Sim Zacks]

PostGreSQL 8.0beta1
I have a function that uses a cursor and it is giving me the error: cursor
crsr already in use when the parameters I pass in come from another table.

The function works fine when I call it by itself, such as select
PartNeedsReschedule(100,1) or select * from PartNeedsReschedule(100,1)
but when I try select PartNeedsReschedule(PartID,1) from Parts then I get
the error.

It seems that when I qualify the query such as PartNeedsReschedule(PartID,1)
from Parts where partid=100
then it works because it is only returning one row.

Is this a known problem with cursor based functions that they can not be
called by set based queries?
Is there a workaround?

Thank You
Sim



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Cursor bug?

[Martijn van Oosterhout]

On Mon, Jan 17, 2005 at 12:06:56AM -0600, Mike G. wrote:
 It doesn't look like it has been added to the documentation yet.  The
 only reference I could find to it was in the todo list (create
 similiar ability for delete statement).
 
 USING allows you to add join statements to your update statement and
 use the data in the columns of the joined tables as input into the
 table the update is performed against.
 
 http://manuals.sybase.com/onlinebooks/group-as/asg1/250e/sqlug/@Generic_BookTextView/27741

This link doesn't work for me, nothing happens. Any other references?
I'm trying to understand what it allows that a FROM-clause doesn't.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpgBoOZtuMHK.pgp
Description: PGP signature

Re: [GENERAL] Cursor bug?

[Mike G.]

On Fri, Jan 14, 2005 at 01:28:47PM +0100, Martijn van Oosterhout wrote:
 On Thu, Jan 13, 2005 at 04:35:04PM -0600, Mike G. wrote:
  thank you.
  
  I use the cursor because I really do an update against a different
  table based on a value from the select in the original table.  I am
  eagerly awaiting 8.0 and the ability to issue an Update Table1 Set
  Table1.col = Table2.col Using (Select y from Table2)
 
 What's this USING keyword you're referring to, I can't find it in the
 documentation anywhere, what does it do?

It doesn't look like it has been added to the documentation yet.  The only 
reference I could find to it was in the todo list (create similiar ability for 
delete statement).

USING allows you to add join statements to your update statement and use the 
data in the columns of the joined tables as input into the table the update is 
performed against.

http://manuals.sybase.com/onlinebooks/group-as/asg1/250e/sqlug/@Generic_BookTextView/27741

 
 Beside, it's seems to me your entire function could be replaced by a
 single query:
 
 insert into temp_tables.delete_me
  select name, file_number, question_to_change,
 new_result, email
  from source_table
  where question_to_change = 'consultant' 
  and file_number is not NULL;
 
 or something like that, i didn't check the schema. But this is probably
 a cut down version. Tom's suggestion to use a FOR IN SELECT loop is a
 good one.
 
 Hope this helps,
 -- 
 Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
  Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
  tool for doing 5% of the work and then sitting around waiting for someone
  else to do the other 95% so you can sue them.



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Cursor bug?

[Martijn van Oosterhout]

On Thu, Jan 13, 2005 at 04:35:04PM -0600, Mike G. wrote:
 thank you.
 
 I use the cursor because I really do an update against a different
 table based on a value from the select in the original table.  I am
 eagerly awaiting 8.0 and the ability to issue an Update Table1 Set
 Table1.col = Table2.col Using (Select y from Table2)

What's this USING keyword you're referring to, I can't find it in the
documentation anywhere, what does it do?

Beside, it's seems to me your entire function could be replaced by a
single query:

insert into temp_tables.delete_me
 select name, file_number, question_to_change,
new_result, email
 from source_table
 where question_to_change = 'consultant' 
 and file_number is not NULL;

or something like that, i didn't check the schema. But this is probably
a cut down version. Tom's suggestion to use a FOR IN SELECT loop is a
good one.

Hope this helps,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpqUewSR2JiF.pgp
Description: PGP signature

Re: [GENERAL] Cursor bug?

[Tom Lane]

Mike G. [EMAIL PROTECTED] writes:
 I have a function that reads through one table via a cursor to make updates 
 to a different table.  There is a row in this table with a column that has a 
 null value.  The following row, in that same column, is not null.  
 When the cursor loops through the table it seems to be treating the non null 
 row as being null.  The fetch should be inserting the new non value into the 
 variable but doesn't appear to be.

You did select count(name), which counts the number of non-null
occurrences of name, ie 2.  So the loop stops after two iterations,
and never gets to the last row.

I'd lose the count() step entirely and have the loop be driven solely by
the cursor.  Actually, why use a cursor at all, as opposed to a FOR IN
SELECT loop?

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org