PostgreSQL cursor with arguments

Question

As the documentation describes here, I need to declare a cursor that accepts arguments at OPEN time.

My query looks something similar to:

DECLARE cur CURSOR (argName character varying) FOR SELECT * FROM "TableName" WHERE "SomeColumn" = argName;

When I do this, I get a lovely error:

ERROR:  syntax error at or near "("
LINE 1: DECLARE cur CURSOR (argName character varying) FOR SELECT * FROM...
                           ^

It seems that PostgreSQL is not accepting this form of cursor declaration. Is there any way to solve this? Any workaround?

Clodoaldo Neto · Accepted Answer · 2013-07-23 11:46:39Z

5

That syntax is only valid inside plpgsql functions.

http://www.postgresql.org/docs/current/static/plpgsql-cursors.html

answered Jul 23, 2013 at 11:46

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gcontreras Over a year ago

I marked your answer as accepted, but I changed it to Igor's because yours just states my mistake, while his actually offers a workaround, which was what I was looking for.

Ihor Romanchenko · Accepted Answer · 2013-07-23 11:46:34Z

3

Try something like:

DECLARE 
  argName varchar;
  cur CURSOR FOR SELECT * FROM "TableName" WHERE "SomeColumn" = argName;

The argName will be taken into the query when you OPEN this cursor.

answered Jul 23, 2013 at 11:46

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

3 Comments

gcontreras Over a year ago

Thanks! That looks promising.

gcontreras Over a year ago

Question: I should CLOSE and OPEN cur after changing argName to a new value, right?

Ihor Romanchenko Over a year ago

@gcontreras Yes. When cursor is opened it does not changes its query and params.

francisco · Accepted Answer · 2018-02-23 20:27:47Z

1

Excerpt from: https://www.postgresql.org/docs/current/static/plpgsql-cursors.html

DECLARE
    key integer;
    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
    key := 42;
    OPEN curs4;

From:

answered Feb 23, 2018 at 20:27

francisco

1132 silver badges12 bronze badges

Collectives™ on Stack Overflow

PostgreSQL cursor with arguments

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related