Reuse Cursor Declaration in postgres

Question

Is there a way to reuse the cursor declaration in postgres.

For example : I have a function like below, I am trying to use the declaration of curs1 for curs2 is it possible ?

create or replace function vin_temp_test(k date,x varchar) RETURNS numeric[] AS $$
declare
curs1 CURSOR FOR select prod_name,sum(item_val) sum_value from temp_table group by prod_name;
curs2 cursor curs1;

begin
null
end;

$$ LANGUAGE plpgsql VOLATILE;

Pavel Stehule · Accepted Answer · 2014-02-25 18:32:38Z

1

Directly it is not possible

but you can simplify code by using views

you can use dynamic unbound queries

DECLARE 
  c1 refcursor;
  c2 refcursor;
  sqlstr text;
BEGIN
  sqlstr := 'SELECT ... ';
  OPEN c1 FOR EXECUTE sqlstr;
  OPEN c2 FOR EXECUTE sqlstr;

Important question is - what do you mean 'reuse a cursor'"?

Maybe you can use scrollable cursor with possible reset

There is a statement MOVE

MOVE FIRST IN curs1 -- reset cursor curs1

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

edited Feb 25, 2014 at 18:32

answered Feb 25, 2014 at 17:43

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

Reuse Cursor Declaration in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related