PostgreSQL select sum inside procedure-CodePudding

I want a procedure that checks, which buyer spent more than 1600$ and than I want to print out their info, so I could send them a gift card. I get error: SQL Error [42601]: ERROR: query has no destination for result data when I call this procedure:

create or replace procedure bookstore.procedure1 () 
language plpgsql
as $$
declare 
    i integer := 0;
    temp_price numeric;
    temp_sum numeric;
    temp_foreign_key integer;
    number_of_buyers integer := (select count(*) from bookstore.buyer);
    
begin
    
    while i < number_of_buyers loop
        
        select sum (price) as temp_sum from bookstore.receipt where id_buyer = i;
        
        if temp_sum > 1600 then
            select id_buyer, name, surname, adress from bookstore.buyer where id_buyer = i;
        end if;
        i := i 1;
        
    end loop;

end

$$;

The problem is in select sum (price) as temp_sum... row. I have the similar procedure in MySQL and it works. I tried running that row with id_buyer = 20 for example and it worked. How should I change it?

That's my first question on stackoverflow, I hope it's understandable.

CodePudding user response：

You probably need to add the return statement

In the Function creation definition SQL, there must be a row written as RETURNS integer/text

Try-

language plpgsql
as $$
declare 
    i integer := 0;
    temp_price numeric;
    temp_sum numeric;
    temp_foreign_key integer;
    number_of_buyers integer := (select count(*) from bookstore.buyer);
    
begin
    
    while i < number_of_buyers loop
        
        select sum (price) as temp_sum from bookstore.receipt where id_buyer = i;
        
        if temp_sum > 1600 then
            select id_buyer, name, surname, adress from bookstore.buyer where id_buyer = i;
        end if;
        i := i 1;
        
    end loop;

return 1; 
end
$$;

Or else you can remove the initial statement of RETURNS text/integer by using CREATE OR REPLACE FUNCTION your_function ...

CodePudding user response：

In plpgsql SELECT statement must have a receiver. You must redirect the SELECT output with INTO or use direct affectation

--Fist version
SELECT SUM(price) FROM bookstore.receipt WHERE id_buyer = i INTO temp_sum;
--Second version, keep surrounding parenthesis
temp_sum = (SELECT SUM(price) FROM bookstore.receipt WHERE id_buyer = i);

Regarding your loop, you should use something more efficient and safe

DECLARE
    temp_sum         NUMERIC;
    v_buyer          bookstore.BUYER;
BEGIN

    FOR v_buyer IN SELECT * FROM bookstore.buyer LOOP
        --Use coalesce to manage empty result
        temp_sum = (SELECT coalesce(SUM(price), 0) FROM bookstore.receipt WHERE id_buyer = v_buyer.id);

        IF temp_sum > 1600 THEN
            -- Your business logic
        END IF;

    END LOOP;
END

If your dataset is large it's better to use a cursor

DECLARE
    temp_sum NUMERIC;
    v_buyer_cursor CURSOR FOR SELECT * FROM bookstore.buyer;
    v_buyer  bookstore.BUYER;
BEGIN

    FOR v_buyer IN v_buyer_cursor LOOP

        temp_sum = (SELECT COALESCE(SUM(price), 0) FROM bookstore.receipt WHERE id_buyer = v_buyer.id);

        IF temp_sum > 1600 THEN
            -- Your business logic
        END IF;
        
    END LOOP;
END

CodePudding user response：

I want a procedure that checks, which buyer spent more than 1600$ ...

You do not need a procedure or loop iterations. In SQL the natural way to do that is a query like this:

select id_buyer, name, surname, adress
from bookstore.buyer b
where (
    select sum (price) 
    from bookstore.receipt r
    where r.id_buyer = b.id_buyer
    ) > 1600

... and then I want to print out their info.

But, if you want to learn something about Postgres, you cannot get results from a procedure. You need to create a function, declare its return type and use proper RETURN statements.

create or replace function bookstore.function1() 
returns setof bookstore.buyer   -- !!!
language plpgsql 
as $$
declare 
    i integer := 0;
    temp_price numeric;
    temp_sum numeric;
    temp_foreign_key integer;
    number_of_buyers integer := (select count(*) from bookstore.buyer);
    
begin
    
    while i < number_of_buyers loop
        
        select sum (price) 
        into temp_sum           -- !!!
        from bookstore.receipt 
        where id_buyer = i;
        
        if temp_sum > 1600 then
            return query        -- !!!
            select id_buyer, name, surname, adress 
            from bookstore.buyer 
            where id_buyer = i;
        end if;
        i := i 1;
    end loop;

end
$$;

Use:

select *
from bookstore.function1()

CodePudding user response：

Let SQL do all the work of serving the data. This can be done in a single statement.

select buy.buy_id
     , buy.name  
     , buy.address 
     , buy.city      
     , buy.postal_code 
     , pur.purchases
  from buyers   buy
  join ( select inv.buy_id 
              , sum(inv.amount)  purchases
           from invoices inv
          group by buy_id
          having sum(inv.amount) >= 1600.00          
       ) pur
    on (pur.buy_id = buy.buy_id) ;

If you must have a stored program, then wrap the above query in a SQL function that returns a table. then Select from the function.

create or replace 
function preferred_buyers()
  returns table ( buy_id   integer  
                , name     text 
                , address  text
                , city     text
                , postal_code text
                , purchases numeric(7,2)
                ) 
   language sql
as $$
    select buy.buy_id
         , buy.name  
         , buy.address 
         , buy.city      
         , buy.postal_code 
         , pur.purchases
      from buyers   buy
      join ( select inv.buy_id 
                  , sum(inv.amount)  purchases
               from invoices inv
              group by buy_id
              having sum(inv.amount) >= 1600.00          
           ) pur
        on (pur.buy_id = buy.buy_id) ;
$$;  

select * from preferred_buyers();

Neither of the above handles printing task. This is not something SQL very good at. Actually it cannot do so. Printing requires a programming language extension; for Postgres it is plpgsql. Also realize any printing done is not available at least in a production environment; it would be on the DB server. Handle your printing in the presentation manager (app).