PL/PGSQL

質問

pl/pgsql にて。

select avg(price) into wk_price from uriage_tbl where ymdtm <= current_timestamp and ymdtm >= current_timestamp + '-4 hour';

と４時間前から直近まで価格の平均を求める構文があったとして、この-4 を、希望する任意の数字で変動したいと思っています。
-4 部分を変数にするには、どのようにすればよいか、お分かりの方がいらっしゃればお教え願います。
日付型の計算だと、上記のようにシングルクオーテーション内に引数を持たせるので、その中で変数を持たせるところでつまづいてます。

回答No.2

chukenkenkou

考え方としては、時間型の変数を定義して加減算します。

例えば、
current_timestamp + time '04:00:00'
を変数で行う場合、以下のようにします。

declare vTime time;
vTime:='04:00:00';
current_timestamp + vTime

以下に例を示します。

(1)表定義例
create table uriage_tbl
(ymdtm timestamp without time zone,
price int);

(2)プロシジャ定義例
create or replace function avg_price
(pPM char(1), -- '+' or '-'
pHour time without time zone) -- 'hh:mm:ss'
returns dec(10,2) as $$
declare
wAvg dec(10,2);
begin
raise info 'pPM=%,pHour=%',pPM,pHour;
if pPM='+' then
select into wAvg -- 標準SQLと語順が異なっている
avg(price)
from uriage_tbl
where ymdtm>=current_timestamp
and ymdtm<=current_timestamp + pHour;
elseif pPM='-' then
select into wAvg -- 標準SQLと語順が異なっている
avg(price)
from uriage_tbl
where ymdtm>=current_timestamp - pHour
and ymdtm<=current_timestamp;
else
wAvg:=null;
end if;
raise info 'wAvg=%',wAvg;
return wAvg;
end;
$$ language 'plpgsql'
;

(3)プロシジャ実行例
select avg_price('+','04:00:00');
select avg_price('-','04:00:00');

＜注意＞
PostgreSQL 8.2前後で、select intoの語順が変更になっているようです。

PostgreSQL 8.0では、「select into 変数 avg(列名) from 表名」、
PostgreSQL 8.2では、「select avg(列名) into 変数 from 表名」

上記の例題は、PostgreSQL 8.0での記述になっています。

回答No.1

Dodonpa2

こんにちは。

動的にSQLステートメントを作成してはいかがでしょう。

CREATE OR REPLACE FUNCTION fn_getavg(integer)
RETURNS NUMERIC AS $$
DECLARE
wk_price NUMERIC := -1;
BEGIN
EXECUTE 'SELECT avg(price) '
|| 'FROM public.uriage '
|| 'WHERE ymdtm <= current_timestamp '
|| 'AND ymdtm >= current_timestamp + interval '''
|| to_char($1,'9')
|| ' hour'''
INTO wk_price;

return wk_price;
END;
$$LANGUAGE plpgsql;

-------------------------------------------------------------
こんな感じの関数をつくって、

SELECT public.fn_getavg(-4);
を実行。

お礼

回答案ありがとうございます。なるほどですね、これを参考に作成してみます。
私のこの質問でこのレベルの回答が頂けるとは、とても嬉しい限りです！仕事を手伝って欲しいくらいです。笑。
COBOL上がりでSQL、PL/PGSQL的な考え方が若干苦手であります。。ありがとうございました。