前のページはこちら

【Function編】SQLでテーブル作成と更新

次に関数を作成してSupabaseのデータを取得してみます。今回もこちらを参考にさせていただきました。

Supabaseのデータベースを使うときに役立つ情報

下準備

あらかじめ以前学習ページを行っている想定で進めていきます。

  • 値が入っているgamegenreテーブルとsoftreleaseテーブルがある
  • クエリエディタにMyQueryとTestQueryという名前の2つのクエリがある

まだな方は下記をご参照ください。

【Function編】Functionの作成
【Function編】SQLでテーブル作成と更新

手法について

Supabaseのテーブル取得の手法は様々な方法がありますが、今回は汎用性がありそうなストアドファンクションを記載する形にしたいと思います。

該当するテーブルデータの全取得

関数の追加と実行

MyQueryに新しく次の関数を追加します。終わったらRUNを実行します。

CREATE OR REPLACE Function simple_table_get(year_number int) 
returns SETOF softrelease as
$$
DECLARE
BEGIN
RETURN query SELECT * FROM softrelease WHERE release_year > year_number;
END;
$$
LANGUAGE plpgsql;

その後、TestQueryに下記を記載します。

select simple_table_get(2000);

softreleaseテーブルの中で、release_yearが2000以上のものだけが表示されました。

解説

初出の部分のみピックアップします。

【01】 CREATE OR REPLACE Function simple_table_get(year_number int) 
【02】 returns SETOF softrelease as
【03】 $$
【04】 DECLARE
【05】 BEGIN
【06】 RETURN query SELECT * FROM softrelease WHERE release_year > year_number;
【07】 END;
【08】 $$
【09】 LANGUAGE plpgsql;
  • 【02】…返り値の型として、softreleaseテーブルそのものを指定しています
  • 【06】…RETURN queryでクエリ―結果を返せます。今回はWHEREを使い、softreleaseテーブルのrelease_yearの値が、受け取った引数year_numberより大きいレコードのみを抽出しています。

クエリ構文に関してはネット上に色々と転がっているので探してみると良いかも知れません。

テーブルを連結して返却する

関数の追加と実行

先ほどの例だと、テーブルをそのまま返していたのでジャンルの部分も数値で返っていました。今度は、テーブルを連結してジャンルの部分はジャンル名が入っている状態で返します。

CREATE OR REPLACE Function join_table_get(year_number int) 
returns TABLE (
    game_id int
    , game_genre_name varchar(32)
    , game_name varchar(32)
    , game_release_year int
) as
$$
DECLARE
BEGIN
  RETURN query
  SELECT 
    sl.soft_id
    , gg.genre_name
    , sl.title_name
    , sl.release_year
  FROM
    softrelease as sl
  LEFT JOIN 
    gamegenre as gg
  ON
    sl.genre_id = gg.genre_id
 WHERE sl.release_year > year_number;
END;
$$
LANGUAGE plpgsql;

TestQueryの方には下記を記載して実行します。

select join_table_get(2000);

今度はジャンル名も出てきました。

解説

今回も初出の部分のみピックアップします。

【01】　CREATE OR REPLACE Function join_table_get(year_number int) 
【02】　returns TABLE (
【03】　    game_id int
【04】　    , game_genre_name varchar(32)
【05】　    , game_name varchar(32)
【06】　    , game_release_year int
【07】　) as
【08】　$$
【09】　DECLARE
【10】　BEGIN
【11】　  RETURN query
【12】　  SELECT 
【13】　    sl.soft_id
【14】　    , gg.genre_name
【15】　    , sl.title_name
【16】　    , sl.release_year
【17】　  FROM
【18】　    softrelease as sl
【19】　  LEFT JOIN 
【20】　    gamegenre as gg
【21】　  ON
【22】　    sl.genre_id = gg.genre_id
【23】　 WHERE sl.release_year > year_number;
【24】　END;
【25】　$$
【26】　LANGUAGE plpgsql;

TABLE型で返り値の定義

【02】　returns TABLE (
【03】　    game_id int
【04】　    , game_genre_name varchar(32)
【05】　    , game_name varchar(32)
【06】　    , game_release_year int
【07】　) as

結合が行われた独自のテーブルを返すので、TABLE型として返すべきテーブルを定義します。今回は、

  • 整数型の「game_id」(softreleaseテーブルのidを返す予定)
  • 文字列型の「game_genre_name」
    • softreleaseテーブルのgenre_idに紐づいた名前（RPG、アクションetc）をgenre_nameから表示
  • 文字列型の「game_name」(softreleaseテーブルのタイトル名を返す予定)
  • 整数型の「game_release_year」(softreleaseテーブルのリリース年を返す予定)

を定義しています。

FROM句のas

【17】　  FROM
【18】　    softrelease as sl

順番が前後しますが、こちらのasで別名を付けます。softreleaseをslと定義。

JOINとON

【19】　  LEFT JOIN 
【20】　    gamegenre as gg
【21】　  ON
【22】　    sl.genre_id = gg.genre_id

LEFT JOINでgamegenreテーブルを連結しています。as句でggと別名を付けました。

また、ON句でslのgenre_idとgg.genre_idを結合条件として記載しています。

SELECT

【12】　  SELECT 
【13】　    sl.soft_id
【14】　    , gg.genre_name
【15】　    , sl.title_name
【16】　    , sl.release_year

前後してしまいましたが、SELECT句で書かれてるslやggはその後のas句で定義されたものでした。

SELECT句は抽出したものが返却されるので、returnsで定義した型と合わせます。

次のページはこちら

【Function編】FlutterからFunctionの呼び出し