withが有効な期間？について

Question

PostgreSQL9.5(Windows10 64bit環境)を使用しています。

PostgreSQLのwith句を使用したストアドプロシージャを作成し、実行しました。
内容は以下のようなものです。

---

CREATE TABLE public.point
(
_id serial,
_date date,
_value integer
)

_id	_date	_value
1	'2018-05-01'	1
2	'2018-05-02'	2
3	'2018-05-03'	3

---

CREATE OR REPLACE FUNCTION public._sample_proc(
    IN _target_date date
)
  RETURNS void AS
$BODY$
DECLARE 
--変数宣言
 _now_val integer;
 _count   integer;
 _arr_id  integer[];
BEGIN

with
x as (
select 
  point._id
 ,point._date
 ,SUM(point._value) over (order by _date) AS _sum_value
  from point
  where _value > 0
  order by _date
)

  select count(*)+1 into _count from x where _sum_value < 2;
  select array_agg(x._id) into _arr_id from x ;

RETURN ;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public._sample_proc(date)
  OWNER TO postgres;

上記を実行すると、

select * from _sample_proc('2018-05-10'::date);

ERROR:  リレーション"x"は存在しません
LINE 1: select array_agg(x._id)              from x
                                                  ^
QUERY:  select array_agg(x._id)              from x
CONTEXT:  PL/pgSQL関数_sample_proc(date)の21行目の型SQL ステートメント

********** エラー **********

ERROR: リレーション"x"は存在しません
SQLステート:42P01
コンテキスト:PL/pgSQL関数_sample_proc(date)の21行目の型SQL ステートメント

と出力されます。

withで定義したxを使用する２つのselectのうち、先に使用しているselectを無効にすると、
実行時のエラーは発生しません。

--  select count(*)+1 into _count from x where _sum_value < 2;
  select array_agg(x._id) into _arr_id from x ;

ストアドでwithを使用するのが不適切なのかもしれませんが、私のwithに対する認識は、
withで定義した問合せ結果を一時的に保持してくれるもの、
という認識です。動作的には、１つ目のselectで一時的な保存が無くなってしまうように見えるのですが
ストアドプロシージャ内で有効にし続ける方法はあるでしょうか。

よろしくお願いします。

Answer 1

withの有効範囲は ; までなので

select count(*)+1 into _count from x where _sum_value < 2;

が範囲です。

VIEWを使ってはいかがでしょうか？

Comments

[hayash-dev]

回答ありがとうございます。
> withの有効範囲は ; まで

ポスグレを使って３年以上経つのに、こんな基本的な事を知らなかった事にショックです。

viewを使う件ですが、サンプルの為、簡単なテーブルや問合せを記載しました。
実際にはもう少し複雑な為、viewに置き換えるのは難しそうです。

質問としては、withが有効な期間が';'までと判明しましたので、ベストアンサーとさせてください。

Answer 2

withの有効な範囲は';'までと判明したので解決とします。