PostgreSQL: システムカタログテーブルと情報スキーマビューを活用したテーブル主キー取得 - 詳細なコード例付き

PostgreSQLにおけるPL/pgSQLでテーブルの主キーを取得する方法

システムカタログテーブルを用いる方法

PostgreSQLには、データベース内の様々な情報が格納されたシステムカタログテーブルと呼ばれるテーブル群が存在します。これらのテーブルを利用することで、PL/pgSQLからテーブルの主キーに関する情報にアクセスすることができます。

具体的には、以下のシステムカタログテーブルを用います。

• pg_constraint：制約に関する情報を格納
• pg_attribute：属性（カラム）に関する情報を格納

これらのテーブルを組み合わせて、以下のPL/pgSQLコードのように記述することで、対象テーブルの主キー列を取得することができます。

CREATE OR REPLACE FUNCTION get_primary_key_columns(tablename text)
RETURNS TABLE AS $$
DECLARE
  _conname text;
  _attnum integer;
  _attname text;
BEGIN
  -- 指定されたテーブル名が存在するかどうかを確認
  IF NOT EXISTS (SELECT * FROM pg_catalog.pg_class t WHERE t.relname = tablename) THEN
    RAISE EXCEPTION WITH MESSAGE = 'テーブル ''' || tablename || ''' が存在しません。';
  END IF;

  -- 主キー制約の名前を取得
  SELECT conname
  INTO _conname
  FROM pg_catalog.pg_constraint c
  WHERE c.contype = 'p' AND c.conrelid = (SELECT oid FROM pg_catalog.pg_class t WHERE t.relname = tablename);

  -- 主キー制約に属する属性の番号を取得
  FOR _attnum IN
    SELECT attnum
    FROM pg_catalog.pg_constraint_col c
    WHERE c.conname = _conname;
  LOOP
    -- 属性番号に対応する属性名を取得
    SELECT attname
    INTO _attname
    FROM pg_catalog.pg_attribute a
    WHERE a.attrelid = (SELECT oid FROM pg_catalog.pg_class t WHERE t.relname = tablename)
      AND a.attnum = _attnum;

    -- 取得した属性名を結果セットに追加
    RETURN ROW(_attname);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

上記コードを例として、usersというテーブルの主キー列を取得してみましょう。

DO $$
BEGIN
  DECLARE
    _primary_keys RECORD;
  CURSOR _cur IS FOR SELECT * FROM get_primary_key_columns('users');
  BEGIN
    FOR _primary_keys IN _cur LOOP
      RAISE NOTICE '主キー列: %', _primary_keys.attname;
    END LOOP;
  END;
END;
$$;

このコードを実行すると、usersテーブルの主キー列であるidが出力されます。

情報スキーマビューを用いる方法

PostgreSQL 9.1以降では、情報スキーマと呼ばれるスキーマが導入されています。このスキーマには、データベース内の様々な情報にアクセスするためのビューが用意されており、テーブルの主キーに関する情報も取得することができます。

具体的には、以下の情報スキーマビューを用います。

• table_pkey：テーブルの主キーに関する情報を格納

CREATE OR REPLACE FUNCTION get_primary_key_columns(tablename text)
RETURNS TABLE AS $$
SELECT column_name
FROM information_schema.table_pkey
WHERE table_name = tablename;
$$ LANGUAGE plpgsql;

上記コードは、システムカタログテーブルを用いる方法で紹介したコードとほぼ同様の機能を提供します。

システムカタログテーブルを用いる方法と情報スキーマビューを用いる方法は、それぞれ以下のような特徴があります。

• システムカタログテーブルを用いる方法
  • より汎用性が高い
  • PostgreSQLのバージョンに依存しない
  • コードが複雑になる
• 情報スキーマビューを用いる方法
  • コードがシンプル
  • PostgreSQL 9.1以降でのみ利用可能

上記を踏まえ、状況に応じて適切な方法を選択することをお勧めします。

補足

• 上記のコード例は、あくまでも一例です。必要に応じて、エラー処理や引数チェックなどのロジックを追加してください。
• PL/pgSQLでシステムカタログテーブルや情報スキーマビューにアクセスする際には、適切な権限が付与されていることを確認する必要があります。

以上が、PostgreSQLにおけるPL/pgSQLでテーブルの主キーを取得する方法

PostgreSQLにおけるPL/pgSQLでテーブルの主キーを取得するサンプルコード

システムカタログテーブルを用いる方法

CREATE OR REPLACE FUNCTION get_primary_key_columns(tablename text)
RETURNS TABLE AS $$
DECLARE
  _conname text;
  _attnum integer;
  _attname text;
BEGIN
  -- 指定されたテーブル名が存在するかどうかを確認
  IF NOT EXISTS (SELECT * FROM pg_catalog.pg_class t WHERE t.relname = tablename) THEN
    RAISE EXCEPTION WITH MESSAGE = 'テーブル ''' || tablename || ''' が存在しません。';
  END IF;

  -- 主キー制約の名前を取得
  SELECT conname
  INTO _conname
  FROM pg_catalog.pg_constraint c
  WHERE c.contype = 'p' AND c.conrelid = (SELECT oid FROM pg_catalog.pg_class t WHERE t.relname = tablename);

  -- 主キー制約に属する属性の番号を取得
  FOR _attnum IN
    SELECT attnum
    FROM pg_catalog.pg_constraint_col c
    WHERE c.conname = _conname;
  LOOP
    -- 属性番号に対応する属性名を取得
    SELECT attname
    INTO _attname
    FROM pg_catalog.pg_attribute a
    WHERE a.attrelid = (SELECT oid FROM pg_catalog.pg_class t WHERE t.relname = tablename)
      AND a.attnum = _attnum;

    -- 取得した属性名を結果セットに追加
    RETURN ROW(_attname);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

DO $$
BEGIN
  DECLARE
    _primary_keys RECORD;
  CURSOR _cur IS FOR SELECT * FROM get_primary_key_columns('users');
  BEGIN
    FOR _primary_keys IN _cur LOOP
      RAISE NOTICE '主キー列: %', _primary_keys.attname;
    END LOOP;
  END;
END;
$$;

情報スキーマビューを用いる方法

CREATE OR REPLACE FUNCTION get_primary_key_columns(tablename text)
RETURNS TABLE AS $$
SELECT column_name
FROM information_schema.table_pkey
WHERE table_name = tablename;
$$ LANGUAGE plpgsql;

PostgreSQLにおけるPL/pgSQLでテーブルの主キーを取得するその他の方法

pg_get_constraintdef() 関数を利用する方法

PostgreSQL 8.4以降では、pg_get_constraintdef() 関数を使用して、制約の定義を取得することができます。この関数を用いることで、主キー制約の定義から主キー列を取得することができます。

CREATE OR REPLACE FUNCTION get_primary_key_columns(tablename text)
RETURNS TABLE AS $$
DECLARE
  _constraint_def text;
BEGIN
  -- 指定されたテーブル名が存在するかどうかを確認
  IF NOT EXISTS (SELECT * FROM pg_catalog.pg_class t WHERE t.relname = tablename) THEN
    RAISE EXCEPTION WITH MESSAGE = 'テーブル ''' || tablename || ''' が存在しません。';
  END IF;

  -- 主キー制約の定義を取得
  SELECT pg_get_constraintdef(conoid)
  INTO _constraint_def
  FROM pg_catalog.pg_constraint c
  WHERE c.contype = 'p' AND c.conrelid = (SELECT oid FROM pg_catalog.pg_class t WHERE t.relname = tablename);

  -- 主キー列をパースして取得
  FOR _attname IN
    SELECT regexp_matches(_constraint_def, E'(?<=\(.*)\w+(?=\))')
  LOOP
    RETURN ROW(_attname);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

動的SQLを用いる方法

PostgreSQLでは、動的SQLと呼ばれる機能を使用して、実行時にSQL文を生成することができます。この機能を利用することで、対象テーブルに応じてSQL文を生成し、主キー列を取得することができます。

CREATE OR REPLACE FUNCTION get_primary_key_columns(tablename text)
RETURNS TABLE AS $$
DECLARE
  _sql text;
  _record RECORD;
  CURSOR _cur IS FOR SELECT * FROM EXECUTE _sql;
BEGIN
  -- 指定されたテーブル名が存在するかどうかを確認
  IF NOT EXISTS (SELECT * FROM pg_catalog.pg_class t WHERE t.relname = tablename) THEN
    RAISE EXCEPTION WITH MESSAGE = 'テーブル ''' || tablename || ''' が存在しません。';
  END IF;

  -- 主キー列を取得するSQL文を生成
  _sql := 'SELECT column_name FROM information_schema.table_pkey WHERE table_name = ''' || tablename || '''';

  -- 生成したSQL文を実行
  EXECUTE _sql INTO _cur;

  -- 取得した結果をループ処理
  FOR _record IN _cur LOOP
    RETURN ROW(_record.column_name);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

PostgreSQL拡張モジュールを利用する方法

PostgreSQLには、様々な機能を提供する拡張モジュールと呼ばれるものが存在します。これらの拡張モジュールの中には、テーブルの主キーを取得する機能を提供するものもあります。

例えば、pg_toolsという拡張モジュールには、pg_get_primarykey()という関数を提供しており、この関数を利用することで、対象テーブルの主キー列を取得することができます。

CREATE OR REPLACE FUNCTION get_primary_key_columns(tablename text)
RETURNS TABLE AS $$
LANGUAGE plpgsql
EXTERNAL NAME pg_tools.pg_get_primarykey;
$$;

それぞれの方法の比較

上記で紹介した3つの方法それぞれには、以下のような特徴があります。

この情報が、PostgreSQLにおけるPL/pgSQL