On Thu, May 11, 2006 at 05:29:02AM +0000, bhavani wrote: > > The following bug has been logged online: > > Bug reference: 2431 > Logged by: bhavani > Email address: [EMAIL PROTECTED] > PostgreSQL version: postgresql 8.10 > Operating system: windowsxp > Description: Error:SELECT query has no destination for result data > Details: > > CREATE OR REPLACE FUNCTION insert_adv_exrate(IN comp_id int4, IN advid int4, > IN currid1 int4, OUT exid int4, OUT exrate float8) RETURNS record > AS $$ > /*$BODY$*/ > declare currid integer; > get_exdetails refcursor; > begin > select exid=max(ex_id) from adv_exrate where comp_id=comp_id and > adv_id=advid ;
Here's your problem. That SELECT is going to return a boolean indicating if exid is equal to max(ex_id). But there's other issues here... > if(coalesce(exid,0)=0) then Why not just IF exid IS NULL THEN ? > exid:=1; > else > exid:=exid+1; > END if; > > open get_exdetails FOR > > select curr_id,exchange_rate from curr_master where comp_id=comp_id; > > LOOP FOR ... LOOP would be a bit easier to write than this. See http://lnk.nu/postgresql.org/9fr.html. But anytime you see a LOOP anywhere near a database you really need to be asking yourself if you're doing the right thing. See below. > FETCH get_exdetails into currid,exrate; Why are you fetching into an OUT parameter? This will only return the last row you fetched, which doesn't seem like a good idea... or are you sure only one row can come back? > > IF NOT FOUND THEN > EXIT; -- exit loop > END IF; > > > insert into adv_exrate values(exid,comp_id,advid,currid,exrate); > > END LOOP; > > CLOSE get_exdetails; A much more performant example of this would be: -- It can be very difficult to differentiate between plpgsql variables -- and field names, so use a prefix to avoid confusion. Likewise, you -- might want to preface all parameters with p_, or ALIAS them. DECLARE v_current_id int; BEGIN SELECT INTO v_current_id max(ex_id) FROM ... ; exid := COALESCE(v_current_id, 0) + 1; INSERT INTO adv_exrate (field list here) SELECT p_exid, p_comp_id, p_advid, curr_id, exchange_rate FROM curr_master WHERE comp_id = p_comp_id ; END; > select exrate=exchange_rate from curr_master where comp_id=comp_id and > curr_id=currid1; > > end; > > /*$BODY$*/ > $$ LANGUAGE 'plpgsql' VOLATILE; > ALTER FUNCTION insert_adv_exrate(IN comp_id int4, IN advid int4, IN currid1 > int4, OUT exid int4, OUT exrate float8) OWNER TO postgres; > > ---------------------------------------------------------------------------- > ------------------------------------------------------------- > > > CREATE OR REPLACE FUNCTION insert_adv_exrate(IN comp_id int4, IN advid int4, > IN currid1 int4, OUT exid int4, OUT exrate float8) RETURNS record > AS $$ > /*$BODY$*/ > declare currid integer; > get_exdetails refcursor; > begin > select exid=max(ex_id) from adv_exrate where comp_id=comp_id and > adv_id=advid ; > > if(coalesce(exid,0)=0) then > exid:=1; > else > exid:=exid+1; > END if; > > open get_exdetails FOR > > select curr_id,exchange_rate from curr_master where comp_id=comp_id; > > LOOP > FETCH get_exdetails into currid,exrate; > > IF NOT FOUND THEN > EXIT; -- exit loop > END IF; > > > insert into adv_exrate values(exid,comp_id,advid,currid,exrate); > > END LOOP; > > CLOSE get_exdetails; > > > select exrate=exchange_rate from curr_master where comp_id=comp_id and > curr_id=currid1; > > end; > > /*$BODY$*/ > $$ LANGUAGE 'plpgsql' VOLATILE; > > > i am using the above procedure in postgre sql. > theprocedure is execting successfully.but when i am giving select > insert_adv_exrate(222222222,1,2); it is giving error as > > > ERROR: SELECT query has no destination for result data > HINT: If you want to discard the results, use PERFORM instead. > CONTEXT: PL/pgSQL function "insert_adv_exrate" line 5 at SQL statement > > > How can i solve this problem > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
