勉強用の、自分用のメモで恐縮ですが。
PostgreSQLのクエリの覚書
PostgreSQLのバージョンは10.5になります。
日付関係
年齢計算
select age('now'::timestamp,'1706-01-17'::timestamp)
人物の年齢に限らず、その出来事がどれくらい前の事か等、何年前の日付なのかすぐ分かるのは便利ですね。
ちなみに1706年1月17日は、ベンジャミン・フランクリンさんのお誕生日になります。
指定した日の次の特定の曜日の日付
たとえば2018年12月1日の、つぎの金曜日は・・・2018年12月7日という日付を得たい場合。
select
cal::date as date
,extract(dow from cal)::int as dow
,case extract(dow from cal)
when 0 then '日'
when 1 then '月'
when 2 then '火'
when 3 then '水'
when 4 then '木'
when 5 then '金'
when 6 then '土'
else '不明' end as dow_name
,extract(dow from cal)::int/6+(5-extract(dow from cal)::int%6) as to_friday
,(cal + interval '1' day * (extract(dow from cal)::int/6+(5-extract(dow from cal)::int%6)))::date as next_friday
from generate_series
('2018-12-01'::timestamp, '2018-12-31'::timestamp, '1 day'::interval) cal;
クエリの中の「5」が金曜日を指しています。ここを変えれば、曜日の指定が可能です。6で割った余りを引いて、6で割った商を足してる感じです。
関数関連
戻り値のテーブルに値を入れて返したいとき
RETURN QUERYでクエリを返すのではなく、値そのものを入れる場合、RETURN NEXTで詰めていく感じでしょうか。
CREATE OR REPLACE FUNCTION sample_return_table()
RETURNS TABLE(
id int
,date date
,value text)
AS
$BODY$
DECLARE
cur CURSOR FOR SELECT * FROM some_table;
rec RECORD;
BEGIN
FOR rec IN cur LOOP
id = rec.id;
date = rec.date;
value = rec.value;
RETURN NEXT;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql';
何か便利そうなクエリを作れましたら、ページを更新させて頂こうと思います。