Cursor

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

"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

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

"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

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.