PostgreSQL上で双方向リスト

-- 双方向リストを格納するtestテーブルを作成
CREATE TABLE test (
  id BIGSERIAL PRIMARY KEY,
  prev BIGINT REFERENCES test (id),
  next BIGINT REFERENCES test (id),
  val text
);

-- 双方向リストにレコードを追加する汎用関数を作成
CREATE OR REPLACE FUNCTION dlist_insert_after (
  dlist_name name,  -- レコード挿入先の双方向リストのテーブル名を指定
  new_prev bigint,  -- どのidのレコードの直後に新しいレコードを追加するか指定
  new_val text
) RETURNS bigint AS $$  -- 新しいレコードに払い出された id を返却
DECLARE
  dlist name := quote_ident(dlist_name);
  new_id bigint;
  new_next bigint;
BEGIN
  BEGIN
    -- 挿入先の直前のレコードが存在するか確認
    EXECUTE 'SELECT next FROM ' || dlist || ' WHERE id = $1'
      INTO STRICT new_next USING new_prev;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    -- 存在しない場合は、新しいレコードのprevとnextをNULLにして追加
    EXECUTE 'INSERT INTO ' || dlist ||
      ' VALUES (DEFAULT, NULL, NULL, $1) RETURNING id'
      INTO new_id USING new_val;
    RETURN new_id;
  END;

  -- 新しいレコードを追加
  EXECUTE 'INSERT INTO ' || dlist ||
    ' VALUES (DEFAULT, $1, $2, $3) RETURNING id'
    INTO new_id USING new_prev, new_next, new_val;

  -- 直前のレコードのnextを新しいレコードのidに更新
  EXECUTE 'UPDATE ' || dlist || ' SET next = $1 WHERE id = $2'
    USING new_id, new_prev;

  -- 直後のレコードのprevを新しいレコードのidに更新
  EXECUTE 'UPDATE ' || dlist || ' SET prev = $1 WHERE id = $2'
    USING new_id, new_next;

  RETURN new_id;
END;
$$ LANGUAGE plpgsql;

-- 双方向リストの初期レコードAAAを挿入
SELECT dlist_insert_after('test', NULL, 'AAA');

-- id=1のレコード直後にCCCのレコードを追加
SELECT dlist_insert_after('test', 1, 'CCC');

-- id=1のレコード直後(id=2のレコードの直前)にBBBのレコードを追加
SELECT dlist_insert_after('test', 1, 'BBB');

-- id=1のレコードから続くレコードを取得
WITH RECURSIVE r AS (
  SELECT *, 1 AS num FROM test WHERE id = 1
UNION ALL
  SELECT test.*, num + 1 AS num FROM test, r WHERE test.id = r.next
)
SELECT * FROM r ORDER BY num;