PostgreSQL関数完全ガイド｜組み込み関数から独自関数まで実例付きで解説

「PostgreSQLにはどんな関数があるの？」
「文字列処理や日付計算をもっと効率的にしたい」
「独自の関数を作成したい」

PostgreSQLは豊富な組み込み関数を提供しており、文字列操作、数値計算、日付処理、JSON操作、配列処理など、あらゆる場面で強力な機能を発揮します。

また、独自の関数を作成することで、さらなる拡張性も実現できます。

この記事では、PostgreSQLの関数について、基本的な使い方から実践的な活用例まで、具体的なコード例を交えながらわかりやすく解説します。

PostgreSQL関数の基本概念

関数とは

PostgreSQLの関数は、入力値に対して処理を行い、結果を返すプログラムです。

SQL文の中で使用することで、複雑な処理を簡潔に表現できます。

関数の種類

組み込み関数

• PostgreSQLに最初から用意されている関数
• 文字列、数値、日付、システム情報など幅広い処理をカバー
• アップデートとともに機能が追加・改善される

ユーザー定義関数

• 開発者が独自に作成する関数
• SQL、PL/pgSQL、Python、Cなど様々な言語で作成可能
• 業務固有の処理を関数化して再利用性を向上

拡張機能の関数

• PostGISやpg_trgm等の拡張機能が提供する関数
• 特定の用途に特化した高度な機能を提供

主要カテゴリ別関数一覧と実例

文字列操作関数

文字列の処理や加工に使用する関数群です。

基本的な文字列操作

サンプルデータの準備

-- テストテーブルの作成
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(255),
    phone VARCHAR(20),
    description TEXT
);

INSERT INTO users (first_name, last_name, email, phone, description) VALUES
    ('太郎', '田中', 'taro.tanaka@example.com', '090-1234-5678', '  プログラマー  '),
    ('花子', '山田', 'HANAKO.YAMADA@TEST.COM', '03-9876-5432', 'デザイナー'),
    ('次郎', '佐藤', 'jiro.sato@company.co.jp', '080.1111.2222', 'マネージャー  '),
    ('三郎', '鈴木', 'saburo@suzuki.net', '070-5555-6666', NULL);

文字列結合・変換

-- 名前の結合
SELECT 
    id,
    CONCAT(last_name, ' ', first_name) AS full_name,
    -- || 演算子でも結合可能
    last_name || ' ' || first_name AS full_name_alt
FROM users;

-- 大文字・小文字変換
SELECT 
    email,
    LOWER(email) AS lowercase_email,
    UPPER(first_name) AS uppercase_name
FROM users;

-- 文字列の切り取り
SELECT 
    email,
    SUBSTRING(email FROM 1 FOR 5) AS email_prefix,
    SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;

文字列の長さ・検索

-- 文字列長とバイト数
SELECT 
    first_name,
    CHAR_LENGTH(first_name) AS char_count,
    OCTET_LENGTH(first_name) AS byte_count,
    POSITION('郎' IN first_name) AS position_of_rou
FROM users;

-- 文字列置換・トリム
SELECT 
    phone,
    REPLACE(phone, '-', '') AS phone_no_hyphen,
    REPLACE(phone, '.', '-') AS phone_normalized,
    TRIM(description) AS trimmed_description
FROM users;

パディング（埋め込み）

-- 左右への文字埋め込み
SELECT 
    id,
    LPAD(id::TEXT, 5, '0') AS padded_id,
    RPAD(first_name, 10, '*') AS padded_name
FROM users;

高度な文字列操作

正規表現を使った検索・置換

-- 正規表現マッチング
SELECT 
    email,
    email ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$' AS is_valid_email,
    REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS digits_only
FROM users;

-- 分割（PostgreSQL 14以降）
SELECT 
    email,
    STRING_TO_ARRAY(email, '@') AS email_parts,
    SPLIT_PART(email, '@', 1) AS username,
    SPLIT_PART(email, '@', 2) AS domain
FROM users;

数値・数学関数

数値の計算や数学的操作に使用する関数群です。

基本的な数学関数

サンプルデータの準備

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    cost DECIMAL(10,2),
    stock_quantity INTEGER,
    weight DECIMAL(8,3)
);

INSERT INTO products (name, price, cost, stock_quantity, weight) VALUES
    ('ノートPC', 125000.00, 95000.00, 15, 1.850),
    ('マウス', 2500.50, 1200.00, 150, 0.120),
    ('キーボード', 8900.00, 4500.50, 75, 0.650),
    ('モニター', 45000.00, 32000.00, 25, 5.200);

基本計算関数

-- 絶対値、切り上げ、切り下げ、四捨五入
SELECT 
    name,
    price,
    cost,
    ABS(price - cost) AS price_difference,
    CEIL(price / 1000.0) AS price_thousands_ceil,
    FLOOR(price / 1000.0) AS price_thousands_floor,
    ROUND(price / cost, 2) AS markup_ratio
FROM products;

-- 平方根、べき乗、対数
SELECT 
    name,
    weight,
    SQRT(weight) AS sqrt_weight,
    POWER(weight, 2) AS weight_squared,
    LN(price) AS ln_price,
    LOG(10, price) AS log10_price
FROM products;

統計・集約関数

-- 最大値、最小値、平均値
SELECT 
    COUNT(*) AS product_count,
    SUM(stock_quantity) AS total_stock,
    AVG(price) AS average_price,
    MIN(cost) AS min_cost,
    MAX(price) AS max_price,
    STDDEV(price) AS price_stddev
FROM products;

-- 条件付き集約
SELECT 
    COUNT(CASE WHEN price > 10000 THEN 1 END) AS expensive_products,
    SUM(CASE WHEN stock_quantity < 50 THEN stock_quantity ELSE 0 END) AS low_stock_total
FROM products;

便利な数値関数

-- GREATEST, LEAST, COALESCE, NULLIF
SELECT 
    name,
    price,
    cost,
    GREATEST(price, cost, 1000) AS max_value,
    LEAST(price, cost, 50000) AS min_value,
    COALESCE(weight, 0.0) AS weight_with_default,
    NULLIF(stock_quantity, 0) AS stock_or_null
FROM products;

-- MOD（剰余）とRANDOM
SELECT 
    id,
    name,
    MOD(id, 3) AS id_mod_3,
    RANDOM() AS random_value,
    RANDOM() * 100 AS random_percentage
FROM products;

日付・時刻関数

日付や時刻の操作・計算に使用する関数群です。

現在日時の取得

基本的な日時取得

-- 各種現在日時の取得
SELECT 
    NOW() AS current_timestamp_tz,
    CURRENT_TIMESTAMP AS current_timestamp,
    CURRENT_DATE AS current_date,
    CURRENT_TIME AS current_time,
    LOCALTIME AS local_time,
    LOCALTIMESTAMP AS local_timestamp;

日付の操作と計算

サンプルデータの準備

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE,
    delivery_date DATE,
    created_at TIMESTAMP DEFAULT NOW(),
    status VARCHAR(20)
);

INSERT INTO orders (order_date, delivery_date, status) VALUES
    ('2025-01-15', '2025-01-18', 'delivered'),
    ('2025-01-20', '2025-01-23', 'shipped'),
    ('2025-01-22', NULL, 'processing'),
    ('2025-01-25', '2025-01-28', 'pending');

日付の抽出と変換

-- 日付要素の抽出
SELECT 
    order_date,
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DAY FROM order_date) AS order_day,
    EXTRACT(DOW FROM order_date) AS day_of_week,  -- 0=日曜日
    DATE_PART('quarter', order_date) AS quarter
FROM orders;

-- 日付の切り捨て
SELECT 
    created_at,
    DATE_TRUNC('year', created_at) AS year_start,
    DATE_TRUNC('month', created_at) AS month_start,
    DATE_TRUNC('day', created_at) AS day_start,
    DATE_TRUNC('hour', created_at) AS hour_start
FROM orders;

日付の計算と比較

-- 期間の計算
SELECT 
    order_date,
    delivery_date,
    delivery_date - order_date AS delivery_days,
    AGE(delivery_date, order_date) AS delivery_interval,
    AGE(NOW()::DATE, order_date) AS days_since_order
FROM orders
WHERE delivery_date IS NOT NULL;

-- 日付の加算・減算
SELECT 
    order_date,
    order_date + INTERVAL '3 days' AS three_days_later,
    order_date - INTERVAL '1 week' AS one_week_before,
    order_date + INTERVAL '2 months' AS two_months_later
FROM orders;

日付フォーマット

-- TO_CHARを使ったフォーマット
SELECT 
    order_date,
    TO_CHAR(order_date, 'YYYY-MM-DD') AS iso_format,
    TO_CHAR(order_date, 'YYYY年MM月DD日') AS japanese_format,
    TO_CHAR(order_date, 'Day, Month DD, YYYY') AS english_format,
    TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') AS timestamp_format
FROM orders;

JSON関数

PostgreSQLの強力なJSON処理機能です。

JSONデータの操作

サンプルデータの準備

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_data JSONB,
    settings JSONB
);

INSERT INTO user_profiles (user_data, settings) VALUES
    ('{"name": "田中太郎", "age": 30, "skills": ["Python", "PostgreSQL", "Docker"], "address": {"city": "東京", "prefecture": "東京都"}}',
     '{"theme": "dark", "language": "ja", "notifications": {"email": true, "sms": false}}'),
    ('{"name": "山田花子", "age": 25, "skills": ["JavaScript", "React", "Node.js"], "address": {"city": "大阪", "prefecture": "大阪府"}}',
     '{"theme": "light", "language": "ja", "notifications": {"email": false, "sms": true}}'),
    ('{"name": "佐藤次郎", "age": 35, "skills": ["Java", "Spring", "MySQL"], "address": {"city": "名古屋", "prefecture": "愛知県"}}',
     '{"theme": "auto", "language": "en", "notifications": {"email": true, "sms": true}}');

JSON要素の抽出

-- 基本的なJSON要素の取得
SELECT 
    id,
    user_data->>'name' AS name,
    user_data->>'age' AS age,
    user_data->'address'->>'city' AS city,
    user_data->'skills' AS skills_json,
    JSONB_ARRAY_LENGTH(user_data->'skills') AS skills_count
FROM user_profiles;

-- JSON配列の展開
SELECT 
    id,
    user_data->>'name' AS name,
    JSONB_ARRAY_ELEMENTS_TEXT(user_data->'skills') AS skill
FROM user_profiles;

JSONの構築と変更

-- JSONオブジェクトの構築
SELECT 
    JSONB_BUILD_OBJECT(
        'user_id', id,
        'name', user_data->>'name',
        'city', user_data->'address'->>'city',
        'skill_count', JSONB_ARRAY_LENGTH(user_data->'skills')
    ) AS user_summary
FROM user_profiles;

-- JSON要素の追加・更新
SELECT 
    id,
    user_data || '{"last_login": "2025-01-20"}' AS updated_data,
    JSONB_SET(user_data, '{age}', '31') AS age_updated
FROM user_profiles
WHERE id = 1;

JSON検索とフィルタリング

-- JSON要素での検索
SELECT *
FROM user_profiles
WHERE user_data->>'age'::INTEGER > 30;

-- JSON配列内の検索
SELECT *
FROM user_profiles
WHERE user_data->'skills' ? 'Python';

-- 複雑な条件での検索
SELECT *
FROM user_profiles
WHERE user_data->'address'->>'prefecture' = '東京都'
  AND settings->'notifications'->>'email' = 'true';

配列関数

PostgreSQLの配列データ型を操作する関数群です。

配列の操作

サンプルデータの準備

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[],
    member_ids INTEGER[],
    monthly_budgets DECIMAL[]
);

INSERT INTO projects (name, tags, member_ids, monthly_budgets) VALUES
    ('Webアプリ開発', ARRAY['web', 'frontend', 'backend'], ARRAY[1, 2, 3], ARRAY[100000, 120000, 110000]),
    ('モバイルアプリ', ARRAY['mobile', 'ios', 'android'], ARRAY[2, 4, 5], ARRAY[80000, 90000, 85000]),
    ('データ分析', ARRAY['data', 'analytics', 'ml'], ARRAY[1, 3, 6], ARRAY[150000, 140000, 160000]);

配列の基本操作

-- 配列の長さと要素アクセス
SELECT 
    name,
    tags,
    ARRAY_LENGTH(tags, 1) AS tag_count,
    tags[1] AS first_tag,
    tags[2:3] AS second_and_third_tags
FROM projects;

-- 配列の展開
SELECT 
    name,
    UNNEST(tags) AS tag,
    UNNEST(member_ids) AS member_id
FROM projects;

-- 配列の検索
SELECT *
FROM projects
WHERE 'web' = ANY(tags);

-- 配列の結合と操作
SELECT 
    name,
    tags || ARRAY['新機能'] AS extended_tags,
    ARRAY_CAT(member_ids, ARRAY[7, 8]) AS extended_members,
    ARRAY_REMOVE(tags, 'frontend') AS tags_without_frontend
FROM projects;

システム情報関数

データベースやシステムの情報を取得する関数群です。

基本的なシステム情報

データベース・スキーマ情報

-- 基本的なシステム情報
SELECT 
    CURRENT_DATABASE() AS database_name,
    CURRENT_SCHEMA() AS current_schema,
    CURRENT_USER AS current_user,
    SESSION_USER AS session_user,
    VERSION() AS postgresql_version;

-- 接続情報
SELECT 
    PG_BACKEND_PID() AS backend_pid,
    INET_CLIENT_ADDR() AS client_ip,
    INET_CLIENT_PORT() AS client_port,
    PG_POSTMASTER_START_TIME() AS server_start_time;

データベースサイズとオブジェクト情報

-- データベースサイズ
SELECT 
    datname AS database_name,
    PG_SIZE_PRETTY(PG_DATABASE_SIZE(datname)) AS database_size
FROM pg_database
WHERE datistemplate = FALSE;

-- テーブルサイズ
SELECT 
    schemaname,
    tablename,
    PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(schemaname||'.'||tablename)) AS total_size,
    PG_SIZE_PRETTY(PG_RELATION_SIZE(schemaname||'.'||tablename)) AS table_size
FROM pg_tables
WHERE schemaname = 'public';

ウィンドウ関数

行同士の関係を分析するための高度な関数群です。

基本的なウィンドウ関数

サンプルデータの準備

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    sales_date DATE,
    salesperson VARCHAR(50),
    amount DECIMAL(10,2),
    region VARCHAR(20)
);

INSERT INTO sales (sales_date, salesperson, amount, region) VALUES
    ('2025-01-01', '田中', 150000, '東京'),
    ('2025-01-01', '山田', 120000, '大阪'),
    ('2025-01-01', '佐藤', 180000, '東京'),
    ('2025-01-02', '田中', 200000, '東京'),
    ('2025-01-02', '山田', 160000, '大阪'),
    ('2025-01-02', '鈴木', 140000, '名古屋'),
    ('2025-01-03', '田中', 170000, '東京'),
    ('2025-01-03', '佐藤', 190000, '東京');

ランキングと順序付け

-- ランキング関数
SELECT 
    sales_date,
    salesperson,
    amount,
    region,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
    RANK() OVER (ORDER BY amount DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank,
    PERCENT_RANK() OVER (ORDER BY amount DESC) AS percent_rank
FROM sales
ORDER BY amount DESC;

-- 地域別ランキング
SELECT 
    sales_date,
    salesperson,
    amount,
    region,
    RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank,
    SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales
ORDER BY region, amount DESC;

LAG・LEAD関数

-- 前後の値との比較
SELECT 
    sales_date,
    salesperson,
    amount,
    LAG(amount) OVER (PARTITION BY salesperson ORDER BY sales_date) AS prev_amount,
    LEAD(amount) OVER (PARTITION BY salesperson ORDER BY sales_date) AS next_amount,
    amount - LAG(amount) OVER (PARTITION BY salesperson ORDER BY sales_date) AS amount_change
FROM sales
ORDER BY salesperson, sales_date;

移動平均・累積計算

-- 移動平均と累積合計
SELECT 
    sales_date,
    salesperson,
    amount,
    AVG(amount) OVER (
        PARTITION BY salesperson 
        ORDER BY sales_date 
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg_3days,
    SUM(amount) OVER (
        PARTITION BY salesperson 
        ORDER BY sales_date 
        ROWS UNBOUNDED PRECEDING
    ) AS cumulative_total
FROM sales
ORDER BY salesperson, sales_date;

関数の確認方法

psqlメタコマンドの使用

基本的なメタコマンド

-- 関数一覧の表示
\df

-- 特定パターンの関数検索
\df *substring*

-- 演算子一覧
\do

-- 集約関数一覧
\da

-- スキーマ別関数一覧
\df public.*

SQLクエリによる関数検索

ユーザー定義関数の確認

-- information_schemaを使用
SELECT 
    routine_name AS function_name,
    routine_type AS type,
    data_type AS return_type,
    routine_definition AS definition
FROM information_schema.routines
WHERE routine_schema = 'public'
ORDER BY routine_name;

pg_procを使った詳細情報

-- pg_procテーブルから詳細情報を取得
SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_catalog.pg_get_function_result(p.oid) AS return_type,
    pg_catalog.pg_get_function_arguments(p.oid) AS arguments,
    CASE
        WHEN p.proisagg THEN 'aggregate'
        WHEN p.proiswindow THEN 'window'
        WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
        ELSE 'normal'
    END AS function_type,
    l.lanname AS language
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
WHERE n.nspname = 'public'
ORDER BY p.proname;

ユーザー定義関数の作成

PL/pgSQL関数の作成例

簡単な計算関数

-- 消費税計算関数
CREATE OR REPLACE FUNCTION calculate_tax(
    price DECIMAL,
    tax_rate DECIMAL DEFAULT 0.10
)
RETURNS DECIMAL AS $$
BEGIN
    RETURN ROUND(price * tax_rate, 0);
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT 
    name,
    price,
    calculate_tax(price) AS tax,
    price + calculate_tax(price) AS total_price
FROM products;

条件分岐を含む関数

-- 商品カテゴリ判定関数
CREATE OR REPLACE FUNCTION get_product_category(price DECIMAL)
RETURNS VARCHAR AS $$
BEGIN
    IF price >= 100000 THEN
        RETURN '高額商品';
    ELSIF price >= 10000 THEN
        RETURN '中価格商品';
    ELSE
        RETURN '低価格商品';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT 
    name,
    price,
    get_product_category(price) AS category
FROM products;

テーブル型を返す関数

-- 売上サマリー関数
CREATE OR REPLACE FUNCTION sales_summary(start_date DATE, end_date DATE)
RETURNS TABLE(
    region VARCHAR,
    total_amount DECIMAL,
    avg_amount DECIMAL,
    sale_count BIGINT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        s.region,
        SUM(s.amount)::DECIMAL,
        AVG(s.amount)::DECIMAL,
        COUNT(*)::BIGINT
    FROM sales s
    WHERE s.sales_date BETWEEN start_date AND end_date
    GROUP BY s.region
    ORDER BY SUM(s.amount) DESC;
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT * FROM sales_summary('2025-01-01', '2025-01-03');

関数の管理

関数の確認と削除

-- 関数の存在確認
SELECT EXISTS(
    SELECT 1 FROM pg_proc 
    WHERE proname = 'calculate_tax'
);

-- 関数の削除
DROP FUNCTION IF EXISTS calculate_tax;
DROP FUNCTION IF EXISTS get_product_category(DECIMAL);
DROP FUNCTION IF EXISTS sales_summary(DATE, DATE);

パフォーマンスと最適化

関数使用時の注意点

インデックスとの相性

-- インデックスが効かない例
SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2025;

-- インデックスが効く例
SELECT * FROM orders WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';

IMMUTABLE・STABLE・VOLATILE の指定

-- IMMUTABLE: 同じ入力に対して常に同じ結果
CREATE OR REPLACE FUNCTION calculate_circle_area(radius DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
    RETURN PI() * radius * radius;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- STABLE: トランザクション内で結果が変わらない
CREATE OR REPLACE FUNCTION get_current_date_string()
RETURNS TEXT AS $$
BEGIN
    RETURN CURRENT_DATE::TEXT;
END;
$$ LANGUAGE plpgsql STABLE;

まとめ

PostgreSQLの関数機能は、データベース操作を効率的かつ強力にするための重要な要素です。

この記事のポイント

豊富な組み込み関数

• 文字列、数値、日付、JSON、配列など幅広い分野をカバー
• 実務で頻繁に使用される実用的な機能
• バージョンアップごとに機能が追加・改善

ウィンドウ関数の活用

• 高度な分析処理を効率的に実行
• ランキング、移動平均、累積計算など
• ビジネスインテリジェンス用途に最適

ユーザー定義関数の作成

• 業務固有のロジックを関数化
• 再利用性とメンテナンス性の向上
• PL/pgSQL、SQL、Python など多様な言語対応

関数の確認・管理方法

• psqlメタコマンドによる簡単な確認
• SQLクエリによる詳細な検索
• システムカタログからの情報取得