動画もあります〜操作系はこれをみると一発！

SQL だって引数が使えた方がいい

当然ですが、SQL だって引数を受け取ってそれを条件指定に使える方が何かと便利です。TypeScript に慣れている我々だったら当然そう考えますよね。それが SQL の Create Function という機能です。

これを使うことでアプリケーション開発がより効率化します。

超基礎

まずは関数と同じく、引数を「受け取らない」SQL関数から始めましょう。

1 を返すだけ

シンプルに 1 を返すだけの SQL 関数です。

-- 関数を作成する
create function get_one() returns integer as
$$
select 1
$$
    LANGUAGE sql
    STABLE;

-- 関数を使う
select get_one(); -- カラム名が get_one になる

select get_one() as my_number; -- カラム名を my_number に指定

select * get_one() as my_number; -- * を使うこともできるが、今回は結果に差はない

ポイントは returns の後ろに、帰ってくる値の type を指定することです。select 1 という指定によって常に 1 つまり type integer が帰ってきますのでここでは returns integer と指定しました。

次に as $$ 中身 $$ までが一塊です。ここが JS の function であれば「返り値」に相当する部分になります。$$ $$ はなんやねん、という話になるのですが、これは JS における ` ` です。 つまりこの中は変数が展開できる文字列ということになります。一体何の変数を展開する必要があるかというと、後ほど説明しますが、当然関数が受け取った引数です。受け取った変数を使うために、この空間を変数が展開できる文字列空間にしているわけです。

今回は特に引数は受け取っていないので、普通に select 文を書いて 1 を返しています。

-- 関数を使う
select get_one(); -- カラム名が get_one になる

select get_one() as my_number; -- カラム名を my_number に指定

select * from get_one(); -- 関数の結果をテーブルとして受け取ることもできる

これを使う場合には、select 文の中で JS の関数と同じように呼び出します。

select * from get_one() のように、実行結果をテーブルとして受け取ることも可能です。むしろこのパターンが普通のパターンです。今回は単純に 1 が帰ってくるだけなのでテーブル的に見えませんが、基本的には SQL 関数を使う場合にはテーブルが帰ってくるパターンが多いと言っていいでしょう。

掛け算をする

特に説明はいらないと思いますが、掛け算もできます。SQL の機能ですから当然ですね。

-- 関数を作成する
create function get_two_by_two() returns integer as
$$
select 2 * 2
$$
    LANGUAGE sql
    STABLE;
    
-- 関数を使う
select get_two_by_two() as my_number;

返り値が数値ではなく文字列

returns text とすることで返り値を数値ではなく、文字列にしてみました。

-- 関数を作成する
create function get_text() returns text as
$$
select 'this is my text'
$$
    LANGUAGE sql
    STABLE;

-- 関数を使う
select get_text() as my_text;

引数を受け取る関数

では本題の引数を受け取る SQL 関数を定義していきましょう。

-- 関数を作成する
create function show_my_number(my_number integer) returns integer as
$$
select my_number
$$
    LANGUAGE sql
    STABLE;

-- 関数を使う
select show_my_number(1234);

ポイントは、引数を受け取る際に、名称 type と指定することです。ここでは my_number という名称で受け取る引数は integer type であると指定しています。

そして受け取った引数をそのまま返しています。

ここで $$ $$ で囲んでいたことが生きてきました。引数である my_number が勝手に展開されていますね。

そして関数を使う時には引数を渡すだけです。簡単だ〜！

-- 関数を作成する
create function show_multiply(number_a integer, number_b integer) returns integer as
$$
select number_a * number_b
$$
    LANGUAGE sql
    STABLE;
    
-- 関数を使う
select show_multiply(7, 8);

引数は何個でも受け取れます。使う時には二つ引数を渡します。

setof で配列を返す

さてここまでは SQL 関数の練習でした。基本的には実務ではこういう値を返すだけの場合は少ないでしょう。

よくあるのは、返ってくる結果がテーブルの場合です。

記事テーブルから「タイトル」に「指定の文字列が含まれている」記事一覧を取得するケースを考えてみましょう。

まずはテーブルとレコードの準備をするために以下のクエリを発行してください。

-- テーブルを作る
create table articles
(
    id      serial not null
        constraint articles_pkey
            primary key,
    title   text   not null,
    content text   not null
);

-- 記事を入れる
insert into articles(title, content)
values ('react is great', 'react is great because ...'),
       ('react hooks is great', 'react hooks is great because ...'),
       ('css in js is great ', 'css in js is great because ...'),
       ('emotion css library  is great', 'emotion is great because ...');

それに対して関数を定義していきます。

-- 定義
create function search_article(search_word text) returns setof articles as
$$
select *
from articles
where title ilike ('%' || search_word || '%')
$$
    LANGUAGE sql
    STABLE;
    
-- 使う場合
select * from search_article('react');

ここでポイントなのは returns setof articles という部分です。今までは returns integer や returns text のように単なる値（正確にはスカラ値）が返ってくると指定していました。しかし今回は、絞り込んで返ってくる結果はテーブル、つまりレコードの集合ですから、そういう場合にはこのように書きます。

「articles テーブルのレコード」の「集合 = set of」が返り値の type ですよ、と指定しているわけです。

次にポイントなのは where の中で ilike を使って、絞り込みをかけている点です。引数として受け取った search_word をここでは使っています。

これで様々な絞り込みワードを受け取れて汎用的に使える SQL 関数ができました！！

Hasura でもこの SQL 関数が生きてくる

なぜこれだけ丁寧に SQL 関数を説明してきたかというと、これを Hasura でフル活用する必要がたびたび出てくることになるからです。Hasura にもこの項目のためのドキュメントがあります。

例えばゲームアプリにおいて一ヶ月の高得点ランキングを取得したい場合には、Hasura が自動生成した Query だけでは不十分で、自分でロジックを定義してあげる必要があります。その場合にこの SQL 関数を駆使する必要があります。是非マスターしてください！