postgresql - ERROR: cursor "<unnamed portal>" does not exist -

according procedure cursor shows 2 rows, when execute query, shows error: cursor "unnamed portal" not exist in image table

create table image (   id numeric(9,0) not null,   nm_code character varying(100) not null,   synonym text not null,   constraint synonym_id primary key (id) ) insert image(             id, nm_code, synonym)     values (01,'13160101','car tyre'); insert image(                 id, nm_code, synonym)         values (01,'10000101','car tyre'); 

legacy master table

create table legacymaster (   legacy_code character varying(20),   source_data text, )  insert legacymaster (legacy_code,source_data) values ('123','with car tyre audi 2000') 

mm_nounmodmaster table structure

create table mm_nounmodmaster (   nm_code character varying(18) not null,   noun character varying(35),   modifier1 character varying(35) not null,   modifier2 character varying(35),   modifier3 character varying(35),   nm_type character(1) not null,   nm_abbr character varying(200),   nm_description character varying(80) not null,   is_template character(1) default 'n'::bpchar,   )       insert mm_nounmodmaster(nm_code, noun, modifier1, modifier2, modifier3, nm_type, nm_abbr,              nm_description,is_template)     values ("10000101","abrasive","--","--","","f","--","abrasive",'y')      insert mm_nounmodmaster(nm_code, noun, modifier1, modifier2, modifier3, nm_type, nm_abbr,                  nm_description,is_template)                 values ("13160101","tyre","automobile","--","","f","tyr,auto","tyre,automobile",'y')     insert mm_nounmodmaster(nm_code, noun, modifier1, modifier2, modifier3, nm_type, nm_abbr,              nm_description,is_template)             values ("10020101","actuator","electrical","--","--","f","actr,ele","actuator,electrical",'y') 

in nounmodmaster table 3 records inserted in image table synonyms contains 'car tyre' 2 nm_codes. when execute function cursor execute 2 rows in nounmodmaster except last row because doesn't match image table

create or replace function func_source_based_nounmod(legacy_code_in character varying)                   returns refcursor                 $body$                     declare                          source text;                         nmcode character varying(50);                         v_parent_rec1 record;                         v_parent_rec2 record;                         item_desc character varying(50);                         noun_t character varying(50);                         mod1 character varying(50);                         cur refcursor;                 begin                     --select source_data source legacymaster source_data=legacy_code_in;                     raise notice '1';                     v_parent_rec1 in(select id,nm_code,synonym image)loop                 raise notice '2';                         --if exists (select source_data legacymaster legacy_code=legacy_code_in)                 raise notice '3';                         if exists (select source_data legacymaster legacy_code=legacy_code_in , source_data ilike '%'||v_parent_rec1.synonym||'%')                 raise notice '4';                             v_parent_rec2 in(select distinct aa.nm_code, nm_type,                         (case when nm_abbr not null nm_abbr else '--' end) nm_abbr,                         nm_description, nm_category,                         (case when image.nm_code not null 'active' else 'inactive' end) status,                         (case when noun not null , noun <> '--' , trim(both ' ' noun) <> '' noun else '' end) ||                          (case when modifier1 not null , modifier1 <> '--' , trim(both ' ' modifier1) <> '' ',' || modifier1 else '' end) ||                         (case when modifier2 not null , modifier2 <> '--' , trim(both ' ' modifier2) <> '' ',' || modifier2 else '' end) ||                         (case when modifier3 not null , modifier3 <> '--' , trim(both ' ' modifier3) <> '' ',' || modifier3 else '' end) nounmod,                         is_template, count(image.nm_code) countofnm_code mm_nounmodmaster aa                             inner join image on image.nm_code=aa.nm_code                             image.synonym =v_parent_rec1.synonym , image.nm_code=v_parent_rec1.nm_code group aa.nm_code,image.nm_code)loop                      --raise notice '%',v_parent_rec2.noun;                     --raise notice '%',v_parent_rec2.modifier1;                         end loop;                         item_desc:=v_parent_rec1.nm_code;                     raise notice '%',item_desc;                     raise notice '%',v_parent_rec2.nm_code;                     raise notice '%',v_parent_rec2.nm_type;                     raise notice '%',v_parent_rec2.nm_abbr;                     raise notice '%',v_parent_rec2.nm_description;                     raise notice '%',v_parent_rec2.nm_category;                     raise notice '%',v_parent_rec2.nounmod;                     raise notice '%',v_parent_rec2.countofnm_code;                         --end if;                         open cur                      select distinct v_parent_rec2.nm_code,v_parent_rec2.nm_type,v_parent_rec2.nm_abbr,v_parent_rec2.nm_description,v_parent_rec2.nm_category,v_parent_rec2.nounmod,v_parent_rec2.countofnm_code;                     close cur;                         end if;                         end loop;                   raise notice '5';                 return cur;                     end;                 $body$ 

by passing legacy_code_in parameter, in legacymaster table, if source data string contains 'car tyre'. show 2 rows compare image table

i need

first of error getting because, unless give cursor name, postgres returns cursor special name "unnamed portal" , can go on 1, 2, 3 etc.

this unhelpful generally, fortunately there easy alternative. pass cursor in parameter function, procedure start:

create or replace function func_source_based_nounmod(legacy_code_in character varying, cur refcursor) 

assuming calling function query window can have code looks like:

begin; select func_source_based_nounmod('123', 'mycursor'); fetch in "mycursor"; commit; 

unfortunately easy bit! given data posted, btw incorrectly formatted , missing columns in function, see passed in not 'car tyre' seemed suggest '123'. necessary because otherwise never open cursor, because have

if exists (select source_data legacymaster  legacy_code=legacy_code_in , 

so legacy_code_in has match code in legacymaster.

then problems worse.

you have count() in following select (for v_parent_rec2 in(select distinct aa.nm_code, nm_type, etc.). means items in select need in group not of them! here had particular problem getting code run, table structure above missing category.

finally in loop. @ end of loop open , close cursor. seem under misapprehension doing so, able fill cursor successive rows. not how works.

similarly return cursor after have closed (hence original error message). in order able access cursor, cursor needs open when return it.

so how fix this? recommendation use temporary table. clear table before calling function. instead of opening , closing cursor @ end of loop, adds row temporary table. , after running function read table. if need have resultset in cursor, can open cursor on temporary table.