勉強用の、自分用のメモで恐縮ですが。

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';

何か便利そうなクエリを作れましたら、ページを更新させて頂こうと思います。