Problem with cursor
От | Frank Millman |
---|---|
Тема | Problem with cursor |
Дата | |
Msg-id | 012d01c337f7$dbd33e50$0401a8c0@chagford обсуждение исходный текст |
Ответы |
Re: Problem with cursor
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
Hi all
I posted this to the newsgroup on June 11th, but I got no replies.
Is the mailing list is a better forum for asking questions of this nature? Below is a script that creates and populates three tables, and then creates a scroll cursor to retrieve the data. Mostly it works fine, but in this precise situation it fails, in the following manner. The "select" returns a cursor with two rows. Results are as follows - 1. move 1 in cursor - ok, skips past first row 2. fetch next from cursor - ok, fetches second row 3. move -2 in cursor - ok, moves to beginning of cursor 4. fetch next from cursor - not ok - should return first row, returns 0 rows (in fact any cursor activity from this point returns 0 rows - all I can do is 'end') If I remove the "order by" clause, or change it to order by anything else, it works correctly. Tested on 7.2.1 and 7.3.2 - results are the same. Any advice will be much appreciated. Frank Millman CREATE TABLE SysUsers ( UserId varchar (999) not null primary key, Password varchar (999), Surname varchar (999) not null, FirstName varchar (999), Administrator char default ' ' check (Administrator in ('Y',' ')), DeleteInd char default ' ' check (DeleteInd in ('Y',' ')) ) ; INSERT INTO SysUsers VALUES ('Admin','','Administrator','','Y',' ') ; INSERT INTO SysUsers VALUES ('Frank','','Millman', 'Frank', ' ',' ') ; INSERT INTO SysUsers VALUES ('Greer','','Millman', 'Greer', ' ',' ') ; CREATE TABLE SysCompanies ( CompanyId varchar (999) not null primary key, CompanyName varchar (999) not null ) ; INSERT INTO SysCompanies VALUES ('ccc', 'System Administration') ; INSERT INTO SysCompanies VALUES ('chagford', 'Chagford cc') ; INSERT INTO SysCompanies VALUES ('chagtest', 'Chagford test company') ; CREATE TABLE SysUsersCompanies ( UserId varchar (999) not null references SysUsers, CompanyId varchar (999) not null references SysCompanies, primary key (UserId, CompanyId) ) ; INSERT INTO SysUsersCompanies VALUES ('Admin', 'ccc') ; INSERT INTO SysUsersCompanies VALUES ('Admin', 'chagford') ; INSERT INTO SysUsersCompanies VALUES ('Admin', 'chagtest') ; INSERT INTO SysUsersCompanies VALUES ('Frank', 'chagford') ; INSERT INTO SysUsersCompanies VALUES ('Frank', 'chagtest') ; INSERT INTO SysUsersCompanies VALUES ('Greer', 'chagford') ; INSERT INTO SysUsersCompanies VALUES ('Greer', 'chagtest') ; begin ; declare fm scroll cursor for select a.CompanyId, b.CompanyName from SysUsersCompanies a, SysCompanies b where UserId = 'Greer' and a.CompanyId = b.CompanyId order by a.CompanyId ; move 1 in fm ; fetch next from fm ; move -2 in fm ; fetch next from fm ;
В списке pgsql-general по дате отправления: