PERFORMのFOUNDを検証。SELECTで行を返せばTRUEになる。FROMのあり無しは関係無い

DROP TYPE IF EXISTS type_sample_set_test CASCADE;
CREATE TYPE type_sample_set_test AS (
  code TEXT
);

CREATE OR REPLACE FUNCTION sample_set_test(
  p_flag BOOLEAN
) RETURNS SETOF type_sample_set_test AS $FUNCTION$
DECLARE
BEGIN
  IF p_flag THEN
    RETURN QUERY SELECT 'x';
  END IF;
END;
$FUNCTION$ LANGUAGE plpgsql;

DROP TYPE IF EXISTS type_sample_set_test2 CASCADE;
CREATE TYPE type_sample_set_test2 AS (
  code TEXT
);

CREATE OR REPLACE FUNCTION sample_set_test2(
) RETURNS SETOF type_sample_set_test2 AS $FUNCTION$
DECLARE
BEGIN
  PERFORM * FROM sample_set_test(p_flag := TRUE);
  IF FOUND THEN
    RETURN QUERY SELECT 'a';
  ELSE
    RETURN QUERY SELECT 'b';
  END IF;

  PERFORM sample_set_test(p_flag := TRUE);
  IF FOUND THEN
    RETURN QUERY SELECT 'a';
  ELSE
    RETURN QUERY SELECT 'b';
  END IF;

  PERFORM * FROM sample_set_test(p_flag := FALSE);
  IF FOUND THEN
    RETURN QUERY SELECT 'a';
  ELSE
    RETURN QUERY SELECT 'b';
  END IF;

  PERFORM sample_set_test(p_flag := FALSE);
  IF FOUND THEN
    RETURN QUERY SELECT 'a';
  ELSE
    RETURN QUERY SELECT 'b';
  END IF;
END;
$FUNCTION$ LANGUAGE plpgsql;

elect * from sample_set_test2();
 code 
------
 a
 a
 b
 b

青柳康平

UUIDv7

UUIDv7 in 22 languagesを参考に書いてみたけど、逆演算作ったら合わなかったので修正。ミリ秒を余計に足してる。

-- UUIDv7を取得する
-- 引数
--   p_now : 現在時間
-- 戻り値
--   UUID
CREATE OR REPLACE FUNCTION uv_uuid_v7(
  p_now TIMESTAMPTZ DEFAULT NULL
) RETURNS UUID AS $$
DECLARE
  w_epoch BIGINT := (extract(epoch from COALESCE(p_now, now())) * 1000)::BIGINT;
BEGIN
  RETURN
    -- timestamp
    lpad(to_hex((w_epoch >> 16)), 8, '0') || '-' ||
    lpad(to_hex((w_epoch & 0xffff)), 4, '0') || '-' ||
    -- version / rand_a
    lpad(to_hex((0x7000 + (random() * 0x0fff)::int)), 4, '0') || '-' ||
    -- variant / rand_b
    lpad(to_hex((0x8000 + (random() * 0x3fff)::int)), 4, '0') || '-' ||
    -- rand_b
    lpad(to_hex((floor(random() * (2^48))::bigint >> 16)), 12, '0');
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION uv_uuid_to_timestamptz(
  p_uuid UUID DEFAULT NULL
) RETURNS TIMESTAMPTZ AS $$
DECLARE
  w_value BIGINT := ('x'||replace(p_uuid::TEXT, '-', ''))::bit(48)::BIGINT;
BEGIN
  RETURN
    to_timestamp(w_value / 1000) + ((w_value % 1000) || ' milliseconds')::INTERVAL
  ;
END;
$$ LANGUAGE plpgsql;

青柳康平

テーブルの内容をJSONにする

select * from articles;
 id | account_id 
----+------------
 10 |          1
 20 |          2

select array_agg(t1.*) from articles as t1;
      array_agg      
---------------------
 {"(10,1)","(20,2)"}

select to_jsonb(array_agg(t1.*)) from articles as t1;
                       to_jsonb                       
-----------------------------------------------------
 [{"id":10,"account_id":1},{"id":20,"account_id":2}]

青柳康平

select
    distinct unnest(t.a)
from
    (values (array[1, 2, 3]), (array[4, 5, 6, 7]), (array[1, 2, 3])) as t(a)
;

結果