title: Executing functions inside other functions
body: <p>I need to create a SINGLE function that looks two different tables (created from joining other tables) BRANDS_TYPES and PRODUCTS_LAUNCH. They have the following Structure.  </p>
  
  <pre><code>| id | brand_name   | type   |  
  | 1  | porsche      | sports |  
  | 1  | porsche      | suvs   |  
  
  | id | brand_name  |  prod   | prod_date | sales  | group | prod_remain |  
  |  1 | porsche     | carrera |      1991 | 500000 |     1 |           5 |  
  </code></pre>
  
  <p>I need to return a table that contains the <strong>brands</strong> in the <strong>top x sales</strong> for a <strong>chosen type</strong> of a <strong>single group</strong>. Brands <em>must have at least 1 product launch since a chosen date</em>. Returning table should sum sales grouped by brand and ordered by sales AND the brands with least remaining products. <strong>I cannot figure out how to join these various queries into a single function.</strong></p>
  
  <p>I've managed to split the "procedure" into three different parts, as they are not overly complicated for a beginner (me). The first function gets all the brands that have a specific type. The second looks at PRODUCTS_LAUNCH and returns brands that have launched at least 1 product after a specific date and belong to a certain group. The third function looks at PRODUCTS_LAUNCH and returns a table with a ranked order of brands with the least amount of remaining products and most sales.</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION get_type (type_choice VARCHAR)  
      RETURNS TABLE (  
      g_type_name brands_types.type%TYPE,   
      g_brand_id brands_types.brand_id%TYPE,  
      g_brand_name brands_types.brand_name%TYPE)  
  AS $$  
  BEGIN  
      RETURN QUERY SELECT   
          type_name,  
          brand_id,  
          brand_name  
      FROM brands_types WHERE type ILIKE type_choice;  
  END; $$     
  </code></pre>
  
  <hr>
  
  <pre><code>CREATE OR REPLACE FUNCTION get_group_date (cutoff_date DATE, group_choice INT)
      RETURNS TABLE ( 
      r_brand_name products_launch.brand_name%TYPE,
      r_date_l products_launch.prod_date%TYPE,
      r_sales products_launch.sales%TYPE,
      r_group products_launch.group%TYPE,
      r_remaining products_launch.prod_remain%TYPE)
  AS $$
  DECLARE
      i_row record;
  BEGIN
      FOR i_row IN 
          (SELECT  
              brand_name,
              prod_date,
              sales,
              group,
              prod_remain
          FROM 
              products_launch
          WHERE prod_date &gt;= cutoff_date AND group = group_choice)
      LOOP
          r_brand_name := upper(i_row.brand_name);
          r_date_l := i_row.prod_date;
          r_sales := i_row.sales;
          r_group := i_row.group;
          r_remaining := i_row.prod_remain;
          RETURN NEXT;
      END LOOP;
  END;
  $$
  </code></pre>
  
  <hr>
  
  <pre><code>CREATE OR REPLACE FUNCTION get_topsales_prodleft (top_t1 INT)
      RETURNS TABLE (
          tp_brand_name products_launch.brand_name%TYPE,
          tp_remaining products_launch.prod_remain%TYPE,  
          tp_sales products_launch.sales%TYPE
          )
  AS $$
  BEGIN 
      RETURN QUERY        
          SELECT 
              brand_name,
              prod_remain,
              SUM (sales) AS total_sales
          FROM
              products_launch
          GROUP BY
              brand_name,
              prod_remain
          ORDER BY prod_remain DESC, total_sales DESC
          LIMIT top_t1;
  END;
  $$
  </code></pre>
  
  <p>Each function works as intended (i think) however i need to have a single function. How do i call one inside another and have its return be queried?</p>