Обсуждение: Cursor
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;
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_count where 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
Richard Huxton <dev@archonet.com> writes:
> Bob Pawley wrote:
>> 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 ;
> 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
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" <tgl@sss.pgh.pa.us> To: "Richard Huxton" <dev@archonet.com> Cc: "Bob Pawley" <rjpawley@shaw.ca>; "PostgreSQL" <pgsql-general@postgresql.org> Sent: Tuesday, July 29, 2008 2:35 PM Subject: Re: [GENERAL] Cursor > Richard Huxton <dev@archonet.com> writes: >> Bob Pawley wrote: >>> 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 ; > >> 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
"Bob Pawley" <rjpawley@shaw.ca> 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
On Tue, Jul 29, 2008 at 5:42 PM, Bob Pawley <rjpawley@shaw.ca> wrote: > begin Don't you need a ; after your begin...? -- - David T. Wilson david.t.wilson@gmail.com
-------------- Original message ----------------------
From: Tom Lane <tgl@sss.pgh.pa.us>
> "Bob Pawley" <rjpawley@shaw.ca> 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
aklaver@comcast.net
Thanks Tom Qualifying the column was the solution. Bob ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Richard Huxton" <dev@archonet.com>; "PostgreSQL" <pgsql-general@postgresql.org> Sent: Tuesday, July 29, 2008 2:51 PM Subject: Re: [GENERAL] Cursor > "Bob Pawley" <rjpawley@shaw.ca> 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
Yes Bob ----- Original Message ----- From: "Adrian Klaver" <aklaver@comcast.net> To: "Tom Lane" <tgl@sss.pgh.pa.us>; "Bob Pawley" <rjpawley@shaw.ca> Cc: "Richard Huxton" <dev@archonet.com>; "PostgreSQL" <pgsql-general@postgresql.org> Sent: Tuesday, July 29, 2008 3:03 PM Subject: Re: [GENERAL] Cursor > -------------- Original message ---------------------- > From: Tom Lane <tgl@sss.pgh.pa.us> >> "Bob Pawley" <rjpawley@shaw.ca> 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 > aklaver@comcast.net >
"Bob Pawley" <rjpawley@shaw.ca> 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
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" <tgl@sss.pgh.pa.us> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Richard Huxton" <dev@archonet.com>; "PostgreSQL" <pgsql-general@postgresql.org> Sent: Tuesday, July 29, 2008 3:30 PM Subject: Re: [GENERAL] Cursor > "Bob Pawley" <rjpawley@shaw.ca> 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
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
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: kgore4@une.edu.au
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.