データベース関数
Postgresには、SQL関数のサポートが組み込まれています。 これらの関数は、データベース内に存在し、APIでも使用できます。
クイック・デモ
はじめに
Supabaseは、データベースの関数を作成するためのいくつかの選択肢を提供しています。 ダッシュボードを使用するか、SQLを使用して直接作成できます。ダッシュボードにはSQLエディターが用意されていますが、データベースに接続して自分でSQLクエリを実行できます。
- 「SQL editor」セクションに移動します。
- 「New Query」をクリックします。
- データベース関数を作成または置き換えるためのSQLを入力します。
- 「Run」をクリックするか、cmd+enter(ctrl+enter)を入力します。
シンプルな関数
文字列「hello world」を返す基本的なデータベース関数を作ってみます。
create or replace function hello_world() -- 1
returns text -- 2
language sql -- 3
as $$ -- 4
select 'hello world'; -- 5
$$; --6
詳細の表示/非表示
最も基本的な関数は、以下の部分で構成されています。
create or replace function hello_world()
:hello_world
は関数の名前です。新規に関数を作成する場合はcreate
、既存の関数を置き換える場合はreplace
を使用します。また、create or replace
を一緒に使用することで、どちらかで処理ができます。return text
:この関数が返すデータの種類です。何も返さない場合は、return void
にします。language sql
:関数本体で使用される言語です。plpgsql
,plv8
,plpython
などの手続き型言語も使用できます。as $$
:関数のラッパーです。$$
記号で囲まれたものは、関数本体の一部となります。select 'hello world';
:単純な関数本体です。関数本体内の最後のselect
文は、それに続く文がない場合に返されます。$$;
:関数ラッパーを閉じるためのシンボルです。
関数が作成された後、その関数を「実行」する方法がいくつかあります。SQLを使ってデータベース内で直接実行するか、クライアントライブラリーの1つを使って実行します。
- SQL
- JS
- Dart
データのセットを返す
データベース関数は、テーブルやビューからデータのセットを返せます。
例えば、スターウォーズのデータの入ったデータベースがあるとします。
- Data
- SQL
create table planets (
id serial primary key,
name text
);
insert into planets (id, name)
values
(1, 'Tattoine'),
(2, 'Alderaan'),
(3, 'Kashyyyk');
create table people (
id serial primary key,
name text,
planet_id bigint references planets
);
insert into people (id, name, planet_id)
values
(1, 'Anakin Skywalker', 1),
(2, 'Luke Skywalker', 1),
(3, 'Princess Leia', 2),
(4, 'Chewbacca', 3);
Planets(惑星)
id | name |
---|---|
1 | Tattoine |
2 | Alderaan |
3 | Kashyyyk |
People(人々)
id | name | planet_id |
---|---|---|
1 | Anakin Skywalker | 1 |
2 | Luke Skywalker | 1 |
3 | Princess Leia | 2 |
4 | Chewbacca | 3 |
例のデータの集合に含まれるすべてのplanets
を返す基本的な例です。
create or replace function get_planets()
returns setof planets
language sql
as $$
select * from planets;
$$;
この関数はテーブルの集合を返すので、フィルタやセレクターを適用できます。例えば、最初の惑星だけが欲しい場合は次のようにします。
- SQL
- JS
- Dart
select *
from get_planets()
where id = 1;
const { data, error } = supabase
.rpc('get_planets')
.eq('id', 1)
final res = await supabase
.rpc('get_planets')
.eq('id', 1)
.execute();
パラメーターを渡す
新しい惑星をplanets
に挿入し、新しいIDを返す基本的な例です。今回はplpgsql
という言語を使っていることに注意してください。
create or replace function add_planet(name text)
returns bigint
language plpgsql
as $$
declare
new_row bigint;
begin
insert into planets(name)
values (add_planet.name)
returning id into new_row;
return new_row;
end;
$$;
繰り返しますが、この関数はselect
クエリを用いてデータベース内部で実行するか、クライアントライブラリーで実行します。
- SQL
- JS
- Dart
select * from add_planet('Jakku');
const { data, error } = await supabase
.rpc('add_planet', { name: 'Jakku' })
final res = await supabase
.rpc('add_planet', params: { 'name': 'Jakku' })
.execute();
Suggestions
データベース・ファンクション vs エッジ・ファンクション
データに関する操作には、データベース内で実行され、RESTやGraphQL APIを使用してリモートで呼びだすことができるデータベース・ファンクションをおすすめします。
グローバルに展開されるTypescriptファンクションをお探しの場合は、エッジ・ファンクション を参照してください。
Security definer
vs invoker
Postgresでは、関数を呼び出したユーザー(invoker
)として実行するか、関数の作成者(definer
)として実行するかを指定できます。例えば次のようにします。
create function hello_world()
returns text
language plpgsql
security definer set search_path = public
as $$
begin
select 'hello world';
end;
$$;
security invoker
を使用するのがベスト・プラクティスとされています(デフォルトではこちらを使用します)。もしsecurity definer
を使う際には、search_path
を設定する必要があります。
これにより、関数を実行するユーザーが持つべきでないスキーマへのアクセスを許可した場合、潜在的な損害を抑えることができます。
リソース
- 公式クライアントライブラリー:JavaScriptとDart
- コミュニティークライアントライブラリー:github.com/supabase-community
- PostgreSQL公式ドキュメント:第9章 関数と演算子
- PostgreSQLリファレンス:CREATE FUNCTION