Advertisement
Guest User

Untitled

a guest
Nov 22nd, 2019
127
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. CREATE TABLE Sales(
  3.     DATE_            date,
  4.     ProdID_         integer,
  5.     OrderID_            integer,
  6.     ManagerID_      integer,
  7.     Sum_            integer
  8. );
  9. DROP FUNCTION testfunc(integer, date);
  10. insert into Sales VALUES (make_date(2019, 2, 4), 1, 1, 1, 63000);
  11.  
  12. CREATE OR REPLACE FUNCTION testfunc(ID_ integer, select_month_ date) RETURNS TABLE (
  13. month_ date,
  14. ManagerID_ integer,
  15. OrderID_ integer,
  16. Sum_ integer
  17. ) AS $$
  18.  
  19. begin  
  20.     RETURN QUERY select ManagerID_ from Sales where orderID = 1;   
  21. end;
  22. $$ LANGUAGE plpgsql;
  23.  
  24.  
  25. DO $$
  26. declare
  27.     d date:= make_date(2020, 1, 1);
  28. BEGIN  
  29. WHILE d < make_date(2020, 1, 1)
  30.     LOOP           
  31.         PERFORM "testfunc"(3, d);
  32.         d:= d + interval '1 month';
  33.     END LOOP;
  34. END $$;
  35.  
  36.  
  37. Drop Table Sales;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement