メインコンテンツまでスキップ

データベース関数

Postgresには、SQL関数のサポートが組み込まれています。 これらの関数は、データベース内に存在し、APIでも使用できます。

クイック・デモ

はじめに

Supabaseは、データベースの関数を作成するためのいくつかの選択肢を提供しています。 ダッシュボードを使用するか、SQLを使用して直接作成できます。ダッシュボードにはSQLエディターが用意されていますが、データベースに接続して自分でSQLクエリを実行できます。

  1. 「SQL editor」セクションに移動します。
  2. 「New Query」をクリックします。
  3. データベース関数を作成または置き換えるためのSQLを入力します。
  4. 「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

詳細の表示/非表示

最も基本的な関数は、以下の部分で構成されています。

  1. create or replace function hello_world()hello_worldは関数の名前です。新規に関数を作成する場合はcreate、既存の関数を置き換える場合はreplaceを使用します。また、create or replaceを一緒に使用することで、どちらかで処理ができます。
  2. return text:この関数が返すデータの種類です。何も返さない場合は、return voidにします。
  3. language sql:関数本体で使用される言語です。plpgsql, plv8, plpythonなどの手続き型言語も使用できます。
  4. as $$:関数のラッパーです。$$記号で囲まれたものは、関数本体の一部となります。
  5. select 'hello world';:単純な関数本体です。関数本体内の最後のselect文は、それに続く文がない場合に返されます。
  6. $$;:関数ラッパーを閉じるためのシンボルです。

関数が作成された後、その関数を「実行」する方法がいくつかあります。SQLを使ってデータベース内で直接実行するか、クライアントライブラリーの1つを使って実行します。

select hello_world();

データのセットを返す

データベース関数は、テーブルやビューからデータのセットを返せます。

例えば、スターウォーズのデータの入ったデータベースがあるとします。

Planets(惑星)

idname
1Tattoine
2Alderaan
3Kashyyyk

People(人々)

idnameplanet_id
1Anakin Skywalker1
2Luke Skywalker1
3Princess Leia2
4Chewbacca3

例のデータの集合に含まれるすべてのplanetsを返す基本的な例です。

create or replace function get_planets() 
returns setof planets
language sql
as $$
select * from planets;
$$;

この関数はテーブルの集合を返すので、フィルタやセレクターを適用できます。例えば、最初の惑星だけが欲しい場合は次のようにします。

select *
from get_planets()
where id = 1;

パラメーターを渡す

新しい惑星を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クエリを用いてデータベース内部で実行するか、クライアントライブラリーで実行します。

select * from add_planet('Jakku');

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を設定する必要があります。 これにより、関数を実行するユーザーが持つべきでないスキーマへのアクセスを許可した場合、潜在的な損害を抑えることができます。

リソース

詳細

データベース関数を作成

JJavaScriptでデータベース関数を呼びだす

外部APIをデータベース関数から呼びだす