将ORDER BY作为参数传递给postgres函数的最佳方式

Question

假设我有一个postgres函数，像这样(...省略号是伪代码)

create or replace function get_people (order_by varchar(64)[])
returns table (id bigint, age smallint, height smallint, weight smallint)
as $$
begin
select
*
from
person
order by
....

假设我想让我的用户像这样通过REST API调用这个函数(伪代码，暂时忽略GET和POST)

$.ajax({type: 'POST',
url: 'example.com/api/get_persons',
data: JSON.stringify({order_by: ['weight ASC', 'height DESC', 'age ASC', 'id ASC']}),
...

实现这一目标的最佳方法是什么?

Answer 1

您必须为要排序的每个可能的列定义一个程序

SELECT
*
from
person
order by
case when 'weight asc' = ANY(orderbyenum) then weight end asc,
case when Paramwheightdesc then height end desc,
....

如果你想让它在一个数组中(例如枚举)，我叫它orderbyparam orderbytype[]，你传递给你的进程:

SELECT
*
from
person
order by
case when 'weight asc' = ANY(orderbyparam) then weight end asc,
case when 'height desc' = ANY(orderbyparam) then height end desc,
....