PostgreSQLチート・データベース関数をクライアントから起動する

• あるWebサービスの開発メモ・目次ページに戻る
• あるWebサービスの開発メモ・PostgreSQLチートに戻る

やりたいこと

PostgreSQL (Supabase データベース) に PLpgSQL ファンクションを配置して クライアントの React Nextjs アプリから起動してデータ登録をしてみたい

実装

(PLpgSQL ファンクション)

create or replace function vote_add(
  id uuid, user_id uuid,  ... (snip)
) 
returns bigint
language plpgsql
as $$
declare 
  new_row bigint;
begin
  insert into table_name(
    id, user_id, ... (snip)
  ) values (
    vote_add.id, vote_add.user_id, ... (snip)
  );

  return new_row;
end;
$$;

このファンクションは supabase の SQL Editor から投入します

(React)

const createComment = async () => {
  rtn = await supabase.rpc("vote_add", {
    id: xxxx.id, user_id: xxxx.user_id, ... (snip)
  });
  console.log(rtn);
};

トラブルシュート

PLpgSQL ファンクションが正常に動作しない場合、その原因はとてもわかりにくいのですが supabase.rpc の返り値を確認すると、ある程度ですが状況がわかります

• error.code
  PostgreSQLのエラーコードが返されます。付録A PostgreSQLエラーコード を確認すると何が問題なのかわかるかもしれません
• error.details
  エラーの意味やヒントを教えてようとしているようです
• error.message
  ここも details 同様に何かを教えようとしているようです

## 謎現象

以下のファンクションコールを実行したところ

rtn = await supabase.rpc("vote_add", { ... }); 

HTTP status が 300 で error.code が "PGRST203" というエラーが発生しました

• HTTP status: 300
• error.code: "PGRST203"
  • このエラーコードは「付録A PostgreSQLエラーコード」に存在しない
• error.hint: "Try renaming the parameters or the function itself in the database so function overloading can be resolved"
  • 関数のオーバーロードを解決できるように、データベース内のパラメーターまたは関数自体の名前を変更してみてください
• error.message: "Could not choose the best candidate function between: public.vote_add(id => text, user_id => text, suki => boolean, username => text, image_url => text, text => text), public.vote_add(id => uuid, user_id => uuid, suki => boolean, username => text, image_url => text, text => text)"
  • ここでは単純に引数とデータベースオブジェクトの型の不一致を教えてくれたらしい

このファンクションが PostgreSQL でどのように設定されているか確認したところ

SELECT pronamespace,proname,proowner,prolang FROM pg_proc WHERE proname LIKE 'vote_add';

pronamespace	proname	proowner	prolang
2200	vote_add	16409	12964
2200	vote_add	16409	12964

なぜか vote_add が２つ存在する

drop すると

drop function vote_add;

Failed to run sql query: function name "vote_add" is not unique

となってしまい、削除できない

違うファンクション名で create function し直したところ、問題なく動作するようになった