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;