ストアド内でテーブル変数のようなものを定義して、そこに値を追加していき、join等で使用する方法

Question

お世話になっております。
現在、SQLServerで書かれたストアドをpostgresqlのストアド(PL/pgSQL)に書き換える作業をしています。
その際に、SQLServerでいうところのテーブル変数がうまく書き換え出来ずに、詰まっています。
ご教示頂ければと思います。

やりたいこととしては、ストアド内でテーブル変数に値をpushしていき、場合によってはそのテーブル変数と他のテーブルをjoinするようなコードを書きたいです。
以下のようなイメージです。↓

CREATE FUNCTION public.test() RETURNS void
AS  $$
  DECLARE 
  -- sqlserverでいうテーブル変数のようなものを宣言　おそらくrecord型？
  recordhensu RECORD;
  -- 変数に追加する行の情報
  gyou INTEGER;

  BEGIN
   -- レコード変数に行を追加していきたいが、方法がわからない。pushのようなメソッドがある？
   gyou := 1;
   rhensu.push(gyou);
   gyou := 2;
   rhensu.push(gyou);

   -- 他のテーブルと、レコード変数とをjoinしたものを取得
   SELECT * FROM other_table INNER JOIN rhensu ON other_table.gyou = rhensu.gyou  
  END;
$$ LANGUAGE plpgsql;

Answer 1

tempにて作成してみました。テーブルで返します。

CREATE OR REPLACE FUNCTION public.test() RETURNS TABLE (Like hoge)
AS  $$
  DECLARE
  gyou INTEGER;

  BEGIN
   -- レコード変数に行を追加していきたいが、方法がわからない。pushのようなメソッドがある？
   create temp table if not exists rhensu (gyou int);
   truncate rhensu;
   gyou := 1;
   insert into rhensu select gyou;
   gyou := 2;
   insert into rhensu select gyou;

   -- 他のテーブルと、レコード変数とをjoinしたものを取得
   RETURN QUERY SELECT hoge.* FROM hoge INNER JOIN rhensu ON hoge.gyou = rhensu.gyou;
  END;
$$ LANGUAGE plpgsql;

select * from test();

Comments

[hiepita1]

回答ありがとうございます。
テーブルを返すことも出来るんですね、参考にしたいと思います。

Answer 2

忠実に再現するなら以下のようになります。
※因みにストアド内でselectする場合には、結果を受け取る記述が必要です。なので下記ではエラーになります。

CREATE FUNCTION public.test() RETURNS void
AS  $$
  DECLARE 
  rhensu integer[]='{}';
  -- 変数に追加する行の情報
  gyou INTEGER;

  BEGIN
   -- レコード変数に行を追加していきたいが、方法がわからない。pushのようなメソッドがある？
   gyou := 1;
   rhensu := rhensu || gyou ;
   gyou := 2;
   rhensu := rhensu || gyou ;

   -- 他のテーブルと、レコード変数とをjoinしたものを取得
   SELECT * FROM other_table INNER JOIN unnest(rhensu) as rhensu(gyou) ON other_table.gyou = rhensu.gyou;  
  END;
$$ LANGUAGE plpgsql;

複数の項目を定義する場合はTYPE定義で構造体を作成し、その構造体を配列で定義します。
多分一時テーブルの方が利用目的には合うかもしれません。
record型は構造体へのポインタ変数に該当し、型自体は持ちません。

Comments

[hiepita1]

ありがとうございます。
record型はポインタ変数であるため、これに対してjoinは出来ないということですね。

一時テーブルを作成するようなイメージが近いので、本来であれば一時テーブルを作成してそれに対してレコードを追加していくような形で行いたいのですが、コード量が増える点と、SQLの問い合わせ回数が増えるので速度面で問題があるのではないか？と感じています。
(一時テーブルを利用する方法だと、Create temp table temp_table 型 ON COMMIT DROP; で作成して、それに対してINSERT INTO temp_table FROM gyou;のような書き方しか思い浮かばないのですが、もっと簡潔にかけますか？）

[sazi]

移植元のテーブル変数への代入は、select結果で代入していると思うので、
create temp table temp_table as select ～のような記述で良いかと思います。
そうでなければ、もう少し具体的な例示をお願いします。

[hiepita1]

返事が遅れてしまって申し訳ありません。
そちらの方法でやってみたいと思います。ありがとうございます！