CODEWITHSUNDEEP
postgresqlplpgsql
jcf
jcf

Reputation: 180

Executing functions inside other functions

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. 

| 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 |

I need to return a table that contains the brands in the top x sales for a chosen type of a single group. Brands must have at least 1 product launch since a chosen date. Returning table should sum sales grouped by brand and ordered by sales AND the brands with least remaining products. I cannot figure out how to join these various queries into a single function.

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.

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; $$     

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 >= 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;
$$

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;
$$

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?

Upvotes: 0

Views: 42

Answers (1)

Bergi
Bergi

Reputation: 664196

First I'd suggest to simplify a bit:

CREATE OR REPLACE FUNCTION get_type (type_choice VARCHAR)  
    RETURNS SETOF brands_types
    LANGUAGE SQL
AS $$
    SELECT *
    FROM brands_types
    WHERE type ILIKE type_choice;  
$$     
CREATE OR REPLACE FUNCTION get_group_date (cutoff_date DATE, group_choice INT)
    RETURNS SETOF products_launch
    LANGUAGE SQL
AS $$
    SELECT
        upper(brand_name) AS brand_name,
        prod_date,
        sales,
        group,
        prod_remain
    FROM 
        products_launch
    WHERE prod_date >= cutoff_date AND group = group_choice;
$$
CREATE OR REPLACE FUNCTION get_topsales_prodleft (top_t1 INT)
    RETURNS SETOF products_launch
    LANGUAGE SQL
AS $$
    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;
$$

Now to join them into a single query, all you need to do is join the type with with launches by brand name, and filter your summation by that:

SELECT *
FROM get_topsales_prodleft($1)
WHERE brand_name IN (SELECT brand_name
                     FROM get_type($2)
                     JOIN get_group_date($3, $4) USING (brand_name))

For this to work as expected, I would think that you a) will need to put the LIMIT on the outer, filtered query not inside the get_topsales_prodleft function, and that you will need to remove the upper() call on the brand_name in get_group_date, or otherwise the join wouldn't work.

Also I believe that it would have been simpler - or at least, a lot less verbose - to write the query in a single statement, instead of using any functions. It would arrive at

SELECT *
    brand_name,
    prod_remain,
    SUM (sales) AS total_sales
FROM
    products_launch
WHERE
    brand_name IN (SELECT brand_name
                   FROM brands_types
                   JOIN products_launch USING (brand_name)
                   WHERE type ILIKE :type_choice
                    AND  prod_date >= :cutoff_date AND group = :group_choice)
GROUP BY
    brand_name,
    prod_remain
ORDER BY prod_remain DESC, total_sales DESC
LIMIT :top_t1;

Upvotes: 1

Related Questions