Postgreql メモ

テーブル情報及びカラム情報の取得

select
   col.table_name
   , (select
      pd.description
      from
         pg_stat_user_tables psut
         , pg_description      pd
      where
         psut.relname = col.table_name
      and
      psut.relid = pd.objoid
      and
      pd.objsubid=0)  as TABLE_COMMENT
   , col.column_name
   , (
      SELECT
           pd.description
       FROM
           pg_stat_all_tables psat
           ,pg_description pd
           ,pg_attribute pa
       WHERE
           psat.schemaname = 'public'
           AND psat.relname = col.table_name
           AND pa.attname = col.column_name
           AND psat.relid = pd.objoid
           AND pd.objsubid >= 0
           AND pd.objoid = pa.attrelid
           AND pd.objsubid = pa.attnum
   )  AS COLUMN_COMMENT
   , is_nullable
   , data_type
   , (
      SELECT
           1
       FROM
           information_schema.table_constraints tc
           ,information_schema.constraint_column_usage ccu
       WHERE
           tc.constraint_type = 'PRIMARY KEY'
           AND tc.table_catalog = ccu.table_catalog
           AND tc.table_schema = ccu.table_schema
           AND tc.table_name = ccu.table_name
           AND tc.constraint_name = ccu.constraint_name
           AND ccu.table_name = col.table_name
           AND ccu.column_name = col.column_name
   ) AS PK
from
   information_schema.columns col
where
   col.table_schema = 'public'
order by
   col.table_name, col.ordinal_position;

現在接続中のクライアントの確認

select * from pg_stat_activity;

現在のコネクション数の確認

SELECT
    datname, usename, COUNT(*)
FROM
    pg_stat_activity
GROUP BY datname, usename;

コネクションの強制切断

select pg_terminate_backend(pid)
from pg_stat_activity
where
   client_addr = /*指定のip*/;

関数を作成時メモ

関数作成するときには以下のような構成で作成する

CREATE OR REPLACE FUNCTION /*関数名*/(/*引数*/) RETURNS /*戻り値の型*/ AS
$$
  DECLARE
    /*変数定義*/
    var_name text;
  BEGIN
    return var_name/*返却値*/
  END ;
$$
LANGUAGE plpgsql;