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

Question

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

Please show the entire query that produced that error. I tried to stitch your two fragments together, but can't get that same error. — jjanes
– jjanes, Commented Aug 26, 2022 at 22:45

jjanes · Accepted Answer · 2022-08-26 22:54:24Z

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.

Will postgres be able to use an index if you order by columnValue * -1?
@Bergi Probably not. But in this case it couldn't anyway as it is a virtual column. If an index were otherwise usable, you would have to resort to the dynamic query to preserve that ability (or just write the query twice with one small difference, and choose between them in their entirely with an pl/pgsql IF.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related