Re: DECLARE CURSOR
От | Matthew V." < |
---|---|
Тема | Re: DECLARE CURSOR |
Дата | |
Msg-id | MhvC9.10398$Q27.8708@nwrddc01.gnilink.net обсуждение исходный текст |
Ответ на | Re: DECLARE CURSOR (Haris Peco <snpe@snpe.co.yu>) |
Список | pgsql-general |
On Mon, 18 Nov 2002 08:28:59 -0600, Haris Peco wrote: > On Monday 18 November 2002 02:27 am, \"Matthew V.\ wrote: >> On Sun, 17 Nov 2002 06:06:05 -0600, snpe wrote: >> > On Sunday 17 November 2002 05:46 am, Frank Miles wrote: >> >> On Sat, 16 Nov 2002, snpe wrote: >> >> > On Saturday 16 November 2002 09:29 pm, Frank Miles wrote: >> >> > > On Fri, 15 Nov 2002, snpe wrote: >> >> > > > Hello, >> >> > > > When I call DECLARE CURSOR out of transaction command >> >> > > > success, >> >> > > > but cursor is not created >> >> > > > Reference manual say that this get error : >> >> > > > ERROR: DECLARE CURSOR may only be used in begin/end >> >> > > > transaction blocks I don't find this text in pgsql source code >> >> > > > What is problem ? >> >> > > >> >> > > According to the documentation for DECLARE CURSOR (v.7.2.x): >> >> > > >> >> > > "Cursors are only available in transactions. Use to BEGIN, >> >> > > COMMIT and >> >> > > ROLLBACK to define a transaction block." >> >> > > >> >> > > This seems consistent with your error message. Please try >> >> > > wrapping your DECLARE inside a transaction using BEGIN,... >> >> > >> >> > I understand it. >> >> > I don't understand why 'DECLARE CURSOR' success out of a >> >> > transaction - I expect error >> >> >> >> What version are you using? At least with 7.2.x, there is an >> >> immediate error at the DECLARE statement. Perhaps I am >> >> misunderstanding your question? >> > >> > 7.3b5 >> > maybe, it is prepare for cursor out of a transaction (I hope) >> >> I'm getting a little confused, here, reading this. I don't have a >> BEGIN, COMMIT, or ROLLBACK in sight in my ESQL application, but my >> cursor works just fine. Under which circumstances are the BEGIN, >> COMMIT, and ROLLBACK required? Is that something specific to the C >> interface? > > You don't use cursor, probably. > For PostgreSQL cursor is explicit with DECLARE CURSOR in sql command It > is like : > BEGIN;; > .. > DECLARE c1 CURSOR FOR SELECT ...; > ... > FETCH 1 FROM c1 > ... > COMMIT; > > > Yes, I do use a cursor. The ESQL I mentioned means "Embedded SQL" (sorry, thought everyone knew). Cursors are a very big part of ESQL. But I don't have any BEGINS, or COMMITS (why would I? I do SELECTs, not INSERTs/UPDATEs/DELETEs!). The cursor declaration, and the subsequent FETCHes, work just fine. That's why I was wondering if I was missing something. The cursor works perfectly the way I wrote it, yet people in this thread keep talking like cursors are only declareable/useable inside transactions (BEGIN-COMMIT blocks).. I personally don't see why you would want to waste transaction overhead unless you are modifying the data (especially since Postgresql doesn't support updateable cursors). In any case, whether or not it's the "correct" behavior, you don't need to specify a BEGIN/COMMIT block to DECLARE a cursor. The documentation the original poster quoted appears to be in error, or outdated (I have the same docs, and they don't match with actual behavior). I declare my cursor in an include file (so that it's global to the file), open the cursor, fetch the cursor until EOF or other error, and process the data. I don't "EXEC SQL BEGIN;" or anything anywhere. Since I'm doing FETCHes, there's no need for a COMMIT. I was just wondering what the hullabaloo was all about, because I don't get any of the errors described by previous posters, and thought maybe I accidentally fixed something, or broke something that was supposed to break my DECLARE... I tried mucking around with autocommit = off/on, but that affects neither the DECLARE nor the FETCH. Is there supposed to be a global autocommit setting? I couldn't find one in the docs for 7.2.1. -- Matthew Vanecek perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);' ******************************************************************************** For 93 million miles, there is nothing between the sun and my shadow except me. I'm always getting in the way of something...
В списке pgsql-general по дате отправления:
Предыдущее
От: "Damjan Pipan"Дата:
Сообщение: plpgsql - accessing field in RECORD variable by text variables