Thread: Cursor

Cursor

From
"Bob Pawley"
Date:
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;

Re: Cursor

From
Richard Huxton
Date:
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

Re: Cursor

From
Tom Lane
Date:
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

Re: Cursor

From
"Bob Pawley"
Date:
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


Re: Cursor

From
Tom Lane
Date:
"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

Re: Cursor

From
"David Wilson"
Date:
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

Re: Cursor

From
aklaver@comcast.net (Adrian Klaver)
Date:
 -------------- 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


Re: Cursor

From
"Bob Pawley"
Date:
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


Re: Cursor

From
"Bob Pawley"
Date:
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
>


Re: Cursor

From
Tom Lane
Date:
"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

Re: Cursor

From
"Bob Pawley"
Date:
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


Re: Cursor

From
Christophe
Date:
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

Re: Cursor

From
Klint Gore
Date:
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


Re: Cursor

From
Christophe
Date:
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.