データベース関数

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つを使って実行します。

SQL

select hello_world();

JS

const { data, error } = await supabase
  .rpc('hello_world')

参考：rpc()

Dart

final res = await supabase
  .rpc('hello_world')
  .execute();

参考：rpc()

データのセットを返す

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

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

Data

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);

SQL

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

select *
from get_planets()
where id = 1;

JS

const { data, error } = supabase
  .rpc('get_planets')
  .eq('id', 1)

Dart

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

select * from add_planet('Jakku');

JS

const { data, error } = await supabase
  .rpc('add_planet', { name: 'Jakku' })

Dart

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

詳細

データベース関数を作成

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

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