0

I am trying to create a procedure (or a query) in plpgsql that will only be run once and here is what I've done so far:

create or replace procedure transfer() 
 LANGUAGE plpgsql  as 
$$
declare 
    cur refcursor;
    trans   record;
    _datum timestamp;
    _price integer;
BEGIN

open cur FOR
select blocknumber, "timestamp"
from transactions
group by blocknumber, "timestamp"
order by blocknumber;

loop

    fetch cur into trans;
    exit when not found;
    
    select _price = "value"
    from public."BNBUSDT"
    where datum > trans.timestamp
    order by datum 
    limit 1;    
    
    update public.transactions
    set "PriceK" = _price
    where blocknumber = trans.blocknumber
      and contract_adresa_kupljenog = '1'
      and "timestamp" = trans.timestamp;
      
end loop;

close cur;

return;

END 
$$ ;

Now when I run this procedure I got this error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function transfer() line 20 at SQL statement
SQL state: 42601

line 20 is

exit when not found;

How to make this working?

Dejan Dozet
  • 948
  • 10
  • 26

1 Answers1

1
select _price = "value"

Here you are trying to return a boolean indicating if those values are equal. There is no destination for that boolean result, hence the error message.

You want:

select "value" into _price
jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Ah, I am from tsql school :) Actually I was trying to assign value to _price variable as you noticed, thanks – Dejan Dozet May 23 '21 at 14:55
  • You from the tsql school! Perhaps taking a [look here](https://blog.sql-workbench.eu/post/migrate-your-mindset/) will help – Belayer May 24 '21 at 22:22