The following bug has been logged online:
Bug reference: 2431
Logged by: bhavani
Email address: pavuluribhavani@yahoo.co.in
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 ;
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;
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