BUG #2431: Error:SELECT query has no destination for result data

The following bug has been logged online:

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

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: pavuluribhavani(at)yahoo(dot)co(dot)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 ;

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 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