Error in postgres Cursor

Question

Please help me to resolve this error.

CREATE OR REPLACE FUNCTION MYCURSOR () RETURNS VARCHAR AS $$
declare

cur1 refcursor;

col_name varchar (10) ;
hstoredata hstore;
BEGIN

col_name = 'id';
OPEN cur1 FOR execute('select * from datas.tb where id =2');
loop
 fetch cur1 into hstoredata;
 if not found then
 exit ;
 end if;
    Raise Notice '%',hstoredata -> col_name ;

 end loop;
 close cur1;
 return 'r';
 END;
 $$ LANGUAGE plpgsql

When i try to execute this query it is showing me the error as

ERROR:  Unexpected end of string
CONTEXT:  PL/pgSQL function "mycursor" line 15 at FETCH

********** Error **********

ERROR: Unexpected end of string
SQL state: XX000
Context: PL/pgSQL function "mycursor" line 15 at FETCH

roman · Accepted Answer · 2013-09-23 06:12:58Z

3

I think this is happening because cursor returning record type, not hstore. You can change your function like:

CREATE OR REPLACE FUNCTION MYCURSOR()
RETURNS VARCHAR AS 
$$
declare
    cur1 refcursor;
    col_name varchar(10);
    rec record;
begin
    col_name := 'id';
    open cur1 for execute('select 1 as id');
    loop
       if not found then
           exit ;
       end if;
       fetch cur1 into rec;
       Raise Notice '%', rec.<column with hstore>-> col_name;
    end loop;
    close cur1;
    return 'r';
end;
$$ LANGUAGE plpgsql;

answered Sep 23, 2013 at 6:12

roman

118k30 gold badges205 silver badges209 bronze badges

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

1 Comment

user2797756 Over a year ago

CREATE OR REPLACE FUNCTION MYCURSOR () RETURNS VARCHAR AS $$ declare cur1 refcursor; var1 varchar (10) ; hstoredata hstore; r record; alert_mesg VARCHAR(2000) := ''; BEGIN var1 = 'id'; OPEN cur1 FOR execute('select * from datas.tb where id =2'); loop fetch cur1 into r; if not found then exit ; end if; select hstore(r) into hstoredata; Raise Notice '%',hstoredata->'id'; end loop; close cur1; return alert_mesg; END; $$ LANGUAGE plpgsql

user2797756 · Accepted Answer · 2013-09-30 12:14:53Z

2

 CREATE OR REPLACE FUNCTION MYCURSOR () RETURNS VARCHAR AS $$
  declare

    cur1 refcursor;
    var1 varchar (10) ;
    hstoredata hstore;
    r record;
    alert_mesg VARCHAR(2000) := '';
       BEGIN

         var1 = 'id';
         OPEN cur1 FOR execute('select * from datas.tb where id =2');
         loop
            fetch cur1 into r;
            if not found then
                 exit ;
            end if;
        select hstore(r) into hstoredata;
            Raise Notice '%',hstoredata->'id';

         end loop;
         close cur1;
         return alert_mesg;
    END;
   $$ LANGUAGE plpgsql

answered Sep 30, 2013 at 12:14

user2797756

731 silver badge7 bronze badges

Collectives™ on Stack Overflow

Error in postgres Cursor

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related