sql - 使用 CASE 和 generate_series() 查询，将结果 timestamptz 降序排列

Question

我是新手，正在尝试使用以下代码创建函数:

CREATE OR REPLACE FUNCTION public.get_bulan()
returns table (request_detail timestamp with time zone)
language plpgsql stable
as $function$
        begin
        return query

        select
        case
        when (extract(DAY FROM now()) >= 25) then generate_series(date_trunc('year', now()), date_trunc('day', now()) ,interval '1 month')
        when (select extract(month FROM now()) = 2) then now() - (interval '1' month * generate_series(0,1))
        when (select extract(month FROM now()) = 1) then now() - (interval '1' month * generate_series(0,2))
        else generate_series((select date(date_trunc('year', now()))), (select date(now())-'1 month'::interval), interval '1 month')
        end
        order by timetstamptz(request_detail) desc;
        end;
        $function$;

上面查询的结果是:

2017-01-01 00:00:00
2017-02-01 00:00:00
2017-03-01 00:00:00
2017-04-01 00:00:00
2017-05-01 00:00:00
2017-06-01 00:00:00
2017-07-01 00:00:00

我尝试使用 order by desc 和 order by timestamp desc 但它不起作用。我想按降序排列，所以我得到了从 2017-07-01 到 2017-01-01 的结果。我该怎么做？

Answer 1

您有两种方法可以做到这一点:

1. 添加as result_timestamp order by result_timestamp desc;

   CREATE OR REPLACE FUNCTION public.get_bulan()
    returns table (request_detail timestamp with time zone)
    language plpgsql stable
    as $function$
       begin
       return query
   
       select 
       case
       when (extract(DAY FROM now()) >= 25) then generate_series(date_trunc('year', now()), date_trunc('day', now()) ,interval '1 month')
       when (select extract(month FROM now()) = 2) then now() - (interval '1' month * generate_series(0,1))
       when (select extract(month FROM now()) = 1) then now() - (interval '1' month * generate_series(0,2))
       else generate_series((select date(date_trunc('year', now()))), (select date(now())-'1 month'::interval), interval '1 month')
       end as result_timestamp order by result_timestamp desc;
   
       end;
       $function$;
   

2. 或者，当您从其他地方调用它时，您可以从过程和订单中删除排序:

   CREATE OR REPLACE FUNCTION public.get_bulan()
   returns table (request_detail timestamp with time zone)
   language plpgsql stable
   as $function$
           begin
           return query
   
           select 
           case
           when (extract(DAY FROM now()) >= 25) then generate_series(date_trunc('year', now()), date_trunc('day', now()) ,interval '1 month')
           when (select extract(month FROM now()) = 2) then now() - (interval '1' month * generate_series(0,1))
           when (select extract(month FROM now()) = 1) then now() - (interval '1' month * generate_series(0,2))
           else generate_series((select date(date_trunc('year', now()))), (select date(now())-'1 month'::interval), interval '1 month')
           end;
   
           end;
           $function$;
   

并调用:

select request_detail from public.get_bulan() order by request_detail desc

注意:第二个非常柔韧，您可以轻松操作。