CODEWITHSUNDEEP
postgresql
Ross
Ross

Reputation: 2417

Postgresql, set order by desc or asc depending on variable parse into function

I have a function that takes product pricing data from today and yesterday and works out the difference, orders it by price_delta_percentage and then limits to 5. Now currently I order by price_delta_percentage DESC which returns the top 5 products that have increased in price since yesterday.

I would like to parse in a variable - sort - to change the function to either sort by DESC, or ASC. I have tried to use IF statements and get syntax errors and CASE statements which states that price_delta_percentage doesn't exist.

Script:

RETURNS TABLE(
    product_id varchar, 
    name varchar, 
    price_today numeric,
    price_yesterday numeric,
    price_delta numeric,
    price_delta_percentage numeric
)
LANGUAGE 'sql'
COST 100
STABLE STRICT PARALLEL SAFE 
AS $BODY$
    WITH cte AS (
        SELECT
            product_id,
            name,
            SUM(CASE WHEN rank = 1 THEN trend_price ELSE NULL END) price_today, 
            SUM(CASE WHEN rank = 2 THEN trend_price ELSE NULL END) price_yesterday,
            SUM(CASE WHEN rank = 1 THEN trend_price ELSE 0 END) - SUM(CASE WHEN rank = 2 THEN trend_price ELSE 0 END) as price_delta,
            ROUND(((SUM(CASE WHEN rank = 1 THEN trend_price ELSE NULL END) / SUM(CASE WHEN rank = 2 THEN trend_price ELSE NULL END) - 1) * 100), 2) as price_delta_percentage
        FROM (
            SELECT
                magic_sets_cards.name,
                pricing.product_id,
                pricing.trend_price, 
                pricing.date, 
                RANK() OVER (PARTITION BY product_id ORDER BY date DESC) AS rank
            FROM pricing
                JOIN magic_sets_cards_identifiers ON magic_sets_cards_identifiers.mcm_id = pricing.product_id
                JOIN magic_sets_cards ON magic_sets_cards.id = magic_sets_cards_identifiers.card_id
                JOIN magic_sets ON magic_sets.id = magic_sets_cards.set_id
            WHERE date BETWEEN CURRENT_DATE - days AND CURRENT_DATE
                AND magic_sets.code = set_code
                AND pricing.trend_price > 0.25) p
        WHERE rank IN (1,2)
        GROUP BY product_id, name
        ORDER BY price_delta_percentage DESC)
    SELECT * FROM cte WHERE (CASE WHEN price_today IS NULL OR price_yesterday IS NULL THEN 'NULL' ELSE 'VALID' END) !='NULL'
    LIMIT 5;
$BODY$;sql

CASE Statement:

ORDER BY CASE WHEN sort = 'DESC' THEN price_delta_percentage END DESC, CASE WHEN sort = 'ASC' THEN price_delta_percentage END ASC)

Error:

ERROR:  column "price_delta_percentage" does not exist
LINE 42:   ORDER BY CASE WHEN sort = 'DESC' THEN price_delta_percenta...

Upvotes: 0

Views: 371

Answers (1)

jjanes
jjanes

Reputation: 44353

You can't use CASE to decide between ASC and DESC like that. Those labels are not data, they are part of the SQL grammar. You would need to do it by combining the text into a string and then executing the string as a dynamic query, which means you would need to use pl/pgsql, not SQL

But since your column is numeric, you could just order by the product of the column and an indicator variable which is either 1 or -1.

Upvotes: 1

Related Questions