Re: Problem with triggers and cursors

On Mon, 11 Sep 2006, Karsten Hoffrath wrote:

> When i try to add the trigger to the database i get the following error:
>
> ERROR:  syntax error at "CURSOR"
> DETAIL:  Expected FOR to open a reference cursor.
> KONTEXT:  compile of PL/pgSQL function "notifytrigger" near line 17
>
> Can someone show me the cause of this error?

http://www.postgresql.org/docs/8.1/interactive/plpgsql-cursors.html seems
to imply that you should not be using CURSOR in the open for query.

Instead of OPEN cRowID CURSOR FOR SELECT nextval('seq_' || TG_RELNAME);
it looks like the correct incantation would be OPEN cRowID FOR
SELECT nextval('seq_' || TG_RELNAME);

However, I'm not sure you really want a cursor in this case.

> One other questions:
> Is using a cursor the preferred way to fetch data from another table?

If you want to loop through many query results in pl/pgsql, you'd possibly
be better off using FOR recordvar IN query LOOP (see the pl/pgsql docs).

For getting a single value, you might be better off with a
variable and something like
SELECT INTO variable nextval('seq' || TG_RELNAME);