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

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

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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461