The following bug has been logged online:

Bug reference: 2431
Logged by: bhavani
Email address: pavuluribhavani (AT) yahoo (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Thu, May 11, 2006 at 05:29:02AM +0000, bhavani wrote:
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...


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.

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?

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;

--
Jim C. Nasby, Sr. Engineering Consultant jnasby (AT) pervasive (DOT) com
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 majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly