Postgresqlにて入力値によって複数のソート条件を指定したい時。

Question

Postgresqlの手続き言語の使用方法について質問です。
以下のようなあるシンプルなクエリにBoolの入力値を元にソート条件が変わるように組みたいのですが、そのフラグによって第二ソート条件まで変えるような書き方がわからないので教えていただきたいです。
上記のような動作にしたいのは、where句内の条件やselectの中身などは共通で使用したいからです。

BEGIN
  RETURN QUERY
  SELECT
    *
  WHERE
    some_table as t1
  OFFSET 0
  LIMIT 20
  ;

END;
$FUNCTION$ LANGUAGE plpgsql;

尚、以下のコードは文法的にはエラーになるのですが、ロジック的にはORDER BY以下のCASE文のような形でクエリを出したいです。
よろしくお願い致します。

DECLARE
  w_flag BOOLEAN := FALSE;
BEGIN
RETURN QUERY
  SELECT
    *
  WHERE
    some_table as t1
  ORDER BY
    CASE WHEN w_flag THEN t1.id ASC, t1.name DESC
    ELSE
      t1.name DESC, t1.name DESC
    END
  OFFSET 0
  LIMIT 20
;

END;
$FUNCTION$ LANGUAGE plpgsql;

Comments

[Orlofsky]

タグ[PL/pgSQL]を追加してください。

Answer 1

エラーの理由は幾つかあります。
・case中にascやdescは記述できない
・case end で;が記述されて文の終わりとなり、以降のoffsetやlimitがエラー

DECLARE
w_flag BOOLEAN := FALSE;
BEGIN
  RETURN QUERY
  SELECT
    *
  WHERE
    some_table as t1
  ORDER BY CASE WHEN w_flag THEN t1.id end asc
         , CASE WHEN w_flag THEN t1.name end DESC
         , CASE WHEN w_flag=False THEN t1.name end desc
         , CASE WHEN w_flag=False THEN t1.id end asc
  OFFSET 0
  LIMIT 20
  ;
END;
$FUNCTION$ LANGUAGE plpgsql

Comments

[daicky777]

ご回答ありがとうございます。
指摘された２点目は完全に転記ミスをしてしまいました。

すいません自分の説明の不備で実現したかったことが伝わっていませんでした。
上記のSQLを元にw_flagがtrueの時はt1.idの昇順、t1.nameの降順、またfalseの時はt1.nameの降順、t1.idの昇順とorder byすることは可能でしょうか？
宜しくお願いいたします。

[sazi]

order byの項目をcaseで分岐させればいいだけです。条件外をNULLにすれば、並び順は影響を受けない
回答を変更しました。

[daicky777]

ありがとうございます！
納得です。

Answer 2

ORDER BY の前に全角空白が入っています。メモ帳でコードを書いているなら、せめてフリーソフトのサクラエディタで全角空白を表示 で使っては？

PostgreSQLの環境がないので、たぶんですが、インラインビューにしては？

DECLARE
  w_flag BOOLEAN := FALSE;
BEGIN
  RETURN QUERY
    SELECT
      *
    FROM(
        SELECT
          *
        WHERE
           some_table as t1
        ORDER BY
           CASE WHEN w_flag THEN t1.id ASC, t1.name DESC
                ELSE
                    t1.name DESC, t1.name DESC
           END
        )
    OFFSET 0
    LIMIT 20 ;
END;
$FUNCTION$ LANGUAGE plpgsql;

Comments

[daicky777]

回答ありがとうございます。

すいません。
転記中に何かおかしなことをしてしまったようです。修正します。
エディタはatomを使用しています。

上記のSQL文ですとcase中にascやdiscが記述されているため、エラーが起きてしまいます。