psqlで配列にloopを回したい。

Question

はじめに

ストアドプロシージャに引数を渡して実行することを考えています。
表現が適切ではなかったらすみません。↑
例えばなのですが、

sql = <<SQL
select
 *
from
 get_list_users(
  ary := ary
　,group := group_id
)
SQL

find_by_sql(sql,ary,group_id)

このような感じで呼び出して実行しようと思っています。
get_list_users.sqlファイルに詳しいsqlが書いてあってそこに引数で配列を渡してloopを回したいです。

###やりたいこと
get_list_users.sqlのなかで
aryの先頭を取り出して

id	aryのデータ	group
1	array.[0]	64cca920-726a-4298-8cf9-66e93d8c7ba1
2	array.[1]	64cca920-726a-4298-8cf9-66e93d8c7ba1
3	array.[2]	64cca920-726a-4298-8cf9-66e93d8c7ba1

このように保存したいです。

psqlで書くことはできますでしょうか？
よろしくお願いします。
psqlでの型はaryがTEXT[],group_idがUUIDです。

Comments

[m.ts10806]

タグにないですがRuby前提でしょうか？

[toriumi_haru]

ruby前提ではないです！
自分が思う「ストアドプロシージャを呼び出して実行」を見ていただいて認識に齟齬がないようにしたかったです。
実際にストアドプロシージャを呼び出すコードはrustなのですが今困っていることとしては
sql側で受け取った配列にループを回すことができるのか
質問内容の表のようにデータを登録するにはどうしたらいいのか
で困っていました！

[toriumi_haru]

どのようなsqlを書けばいいのかで困っています！

Answer 1

unnest()を使用すれば配列を行に展開できます。
WITH ORDINALITYを付加すれば、連番も返却されます。

loopするよりSQLで直接insertなりを行う方が手間が掛からないと思います。

追記

insert対象のテーブルの定義を以下とします

create table user_group(
  id        serial
, user_id   text
, group_id  uuid
);

以下はストアドの定義です。（戻り値は無しです）
3パターン記述していますが、2つはコメントアウトしています。

CREATE OR REPLACE FUNCTION get_list_users(
    ary                    text[]
  , groupid                uuid
)
RETURNS void
AS $BODY$
DECLARE
  wr      record;
BEGIN
/*
  -- 配列の数だけループ
  FOR i IN 1..array_length(ary, 1) LOOP
    insert into user_group(user_id, group_id)
    values (ary[i], groupid)
    ;
  END LOOP;
*/
/*
  -- 配列を行に展開してループ
  FOR wr IN select * from unnest(ary) as w(userid) LOOP
    insert into user_group(user_id, group_id)
    values (wr.userid, groupid)
    ;
  END LOOP;
*/
  -- 配列を行に展開してそのままinsert
  insert into user_group(user_id, group_id)
  select userid, groupid from unnest(ary) as w(userid)
  ;
  return;
END;
$BODY$
LANGUAGE PLpgSQL;

以下のような呼び出しです。

select get_list_users(
  ary       :=array['test01', 'test02', 'test03'] ::text[]
 ,groupid   :='64cca920-726a-4298-8cf9-66e93d8c7ba1' ::uuid
)

Comments

[toriumi_haru]

insertでどのように書いたらいいでしょうか？
group_idも登録したいのですが
unnest()を使用した後どのようにすればいいかが想像つきません。
もう少し教えていただけないでしょうか？

[sazi]

ではそのストアドの中身は良いとして、型が分かるように、パラメータなどの外観を質問に追記して下さい。

[toriumi_haru]

かしこまりました！

[sazi]

追記しました。
find_by_sql()については、insert用のストアドを呼び出すSQLを渡して、何をしようとしているか分かりませんが。
insertと検索を同時に実行？

[toriumi_haru]

ありがとうございました！
丁寧に教えていただいて助かりました！