Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2016
    Posts
    18
    Provided Answers: 1

    Answered: a small function for retrieving of sums

    Hi All,
    In Postgresql 9.4.10 I write a small function for retrieving of sums:

    Code:
    CREATE OR REPLACE FUNCTION public.pd_report_orders_sum_by_categories(p_category_list integer[] DEFAULT NULL::integer[], p_last_operation_date_from timestamp DEFAULT NULL, p_last_operation_date_till timestamp DEFAULT NULL, p_status type_order_status DEFAULT NULL, p_sort_categories character varying DEFAULT 'by_sum_desc', p_limit integer DEFAULT NULL::integer)
    
     RETURNS TABLE(category_name character varying, category_slug character varying, category_id smallint, sold_sum numeric, qty_sum bigint)
     LANGUAGE sql
    AS $function$
    
    SELECT c.name as category_name,
       c.slug as category_slug,
       pc.category_id,
       sum( oi.qty*oi.price ) AS sold_sum,
       sum( oi.qty ) AS qty_sum
    
      from pd_order_item as oi join
    	  pd_product_category as pc on pc.product_id = oi.product_id join
    	  pd_order as o on o.id = oi.order_id join
    	  pd_category as c on c.id = pc.category_id
    
      WHERE ( pc.category_id = ANY (p_category_list) OR p_category_list IS NULL )  AND
     		   ( o.last_operation_date > p_last_operation_date_from OR p_last_operation_date_from IS NULL )  AND
    		   ( o.last_operation_date <= p_last_operation_date_till OR p_last_operation_date_till IS NULL )  AND
    	   ( o.status= p_status OR p_status IS NULL )
    
      group by pc.category_id, category_name, category_slug
    
      ORDER BY
    		CASE WHEN p_sort_categories = 'by_sum_asc' THEN
    			sum( oi.qty*oi.price ) 
    		end 
    		    
    		CASE WHEN p_sort_categories = 'by_sum_desc' THEN
    			sum( oi.qty*oi.price )
    		end,
    		    
    		CASE WHEN p_sort_categories = 'by_sum_category_name' THEN
    			c.name
    		END
    	    
      
      LIMIT p_limit ;
    
    $function$
    It mostly works, but what I search are :
    1) In ORDER BY conditions I want to set asc or desc , but I got syntax error on any attempts. Which is teh correct synatx?

    2) In Case ... End I tried to to use alias of calculated field sold_sum and got error that this field is not found. If there is a way to use alias of calculated field sold_sum here ?

    3) If there is a way if p_limit is null do not set any LIMIT restriction ?

    Thanks!

  2. Best Answer
    Posted by shammat

    "
    Quote Originally Posted by mstdmstd View Post
    1) In ORDER BY conditions I want to set asc or desc , but I got syntax error on any attempts. Which is teh correct synatx?
    You didn't show us your attempts, but it should be something like
    Code:
    order by case ... end desc, case ... end asc, ...
    Quote Originally Posted by mstdmstd View Post
    2) In Case ... End I tried to to use alias of calculated field sold_sum and got error that this field is not found. If there is a way to use alias of calculated field sold_sum here?
    No, not directly you have to use a derived table:
    Code:
    select *
    from (
       .... your original query goes here ....
    ) t
    ORDER BY CASE WHEN p_sort_categories = 'by_sum_asc' THEN old_sum END desc, --<<< don't forget the comma here!
             CASEE WHEN p_sort_categories = 'by_sum_category_name' THEN c.name END asc
    Quote Originally Posted by mstdmstd View Post
    3) If there is a way if p_limit is null do not set any LIMIT restriction?
    Right from the manual: https://www.postgresql.org/docs/curr...html#SQL-LIMIT
    Quote Originally Posted by The fine manual
    If the count expression evaluates to NULL, it is treated as LIMIT ALL, i.e., no limit
    "


  3. #2
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    Quote Originally Posted by mstdmstd View Post
    1) In ORDER BY conditions I want to set asc or desc , but I got syntax error on any attempts. Which is teh correct synatx?
    You didn't show us your attempts, but it should be something like
    Code:
    order by case ... end desc, case ... end asc, ...
    Quote Originally Posted by mstdmstd View Post
    2) In Case ... End I tried to to use alias of calculated field sold_sum and got error that this field is not found. If there is a way to use alias of calculated field sold_sum here?
    No, not directly you have to use a derived table:
    Code:
    select *
    from (
       .... your original query goes here ....
    ) t
    ORDER BY CASE WHEN p_sort_categories = 'by_sum_asc' THEN old_sum END desc, --<<< don't forget the comma here!
             CASEE WHEN p_sort_categories = 'by_sum_category_name' THEN c.name END asc
    Quote Originally Posted by mstdmstd View Post
    3) If there is a way if p_limit is null do not set any LIMIT restriction?
    Right from the manual: https://www.postgresql.org/docs/curr...html#SQL-LIMIT
    Quote Originally Posted by The fine manual
    If the count expression evaluates to NULL, it is treated as LIMIT ALL, i.e., no limit
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •