PL/Python、PL/Perl、PL/v8：PostgreSQL 的多語言程序擴展 | PostgreSQL

-- 確認語言的信任屬性
SELECT lanname, lanpltrusted
FROM pg_language
ORDER BY lanname;

# Ubuntu / Debian
sudo apt install postgresql-plpython3-16

# 在資料庫中啟用
psql -U postgres -d mydb -c "CREATE EXTENSION plpython3u;"

-- 最簡單的 PL/Python 函式
CREATE OR REPLACE FUNCTION py_hello(name text)
RETURNS text
LANGUAGE plpython3u
AS $$
    return f"Hello, {name}!"
$$;

SELECT py_hello('World');
-- 輸出：Hello, World!

-- 使用 Python 標準函式庫
CREATE OR REPLACE FUNCTION py_json_parse(raw_text text)
RETURNS jsonb
LANGUAGE plpython3u
AS $$
    import json
    data = json.loads(raw_text)
    return json.dumps(data)
$$;

-- 使用參數化查詢（防止 SQL Injection）
CREATE OR REPLACE FUNCTION get_user_safe(user_id integer)
RETURNS text
LANGUAGE plpython3u
AS $$
    plan = plpy.prepare(
        "SELECT username, email FROM users WHERE id = $1",
        ["integer"]
    )
    result = plpy.execute(plan, [user_id], 1)

    if not result:
        return None

    return result[0]["username"]
$$;

CREATE OR REPLACE FUNCTION py_generate_series(start_val int, end_val int)
RETURNS SETOF integer
LANGUAGE plpython3u
AS $$
    return range(start_val, end_val + 1)
$$;

SELECT * FROM py_generate_series(1, 5);
-- 輸出：1, 2, 3, 4, 5

CREATE OR REPLACE FUNCTION py_cached_lookup(key text)
RETURNS text
LANGUAGE plpython3u
AS $$
    # GD（Global Data）：同一 Session 內所有 PL/Python 函式共享
    if "cache" not in GD:
        GD["cache"] = {}

    if key in GD["cache"]:
        return GD["cache"][key]

    result = plpy.execute(
        plpy.prepare("SELECT value FROM config WHERE key = $1", ["text"]),
        [key], 1
    )

    if result:
        GD["cache"][key] = result[0]["value"]
        return result[0]["value"]

    return None
$$;

CREATE OR REPLACE FUNCTION py_safe_divide(a numeric, b numeric)
RETURNS numeric
LANGUAGE plpython3u
AS $$
    try:
        if b == 0:
            plpy.error(
                "Division by zero",
                detail="Divisor cannot be zero",
                hint="Use NULLIF(b, 0) to handle this"
            )
        return a / b
    except Exception as e:
        plpy.warning(f"Unexpected error: {e}")
        return None
$$;

# Ubuntu / Debian
sudo apt install postgresql-plperl-16

# 啟用受信任版本
psql -c "CREATE EXTENSION plperl;"

-- 正則表達式提取 email
CREATE OR REPLACE FUNCTION extract_emails(text_content text)
RETURNS text[]
LANGUAGE plperl
AS $$
    my $content = $_[0];
    my @emails;

    while ($content =~ /([a-zA-Z0-9._%+-]+\@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,})/g) {
        push @emails, $1;
    }

    return \@emails;
$$;

SELECT extract_emails('Contact info@example.com or support@company.org');

-- 字串轉 slug
CREATE OR REPLACE FUNCTION perl_slugify(input text)
RETURNS text
LANGUAGE plperl
AS $$
    my $str = lc($_[0]);
    $str =~ s/[^a-z0-9\s-]//g;
    $str =~ s/\s+/-/g;
    $str =~ s/-+/-/g;
    $str =~ s/^-|-$//g;
    return $str;
$$;

SELECT perl_slugify('Hello World! This is a Test.');
-- 輸出：hello-world-this-is-a-test

CREATE OR REPLACE FUNCTION perl_find_user(uid integer)
RETURNS text
LANGUAGE plperl
AS $$
    my $plan = spi_prepare(
        'SELECT username FROM users WHERE id = $1',
        'INTEGER'
    );
    my $rv = spi_exec_prepared($plan, $_[0]);

    if ($rv->{processed} > 0) {
        return $rv->{rows}[0]->{username};
    }
    return undef;  # 回傳 SQL NULL
$$;

CREATE OR REPLACE FUNCTION perl_split_words(sentence text)
RETURNS SETOF text
LANGUAGE plperl
AS $$
    my @words = split(/\s+/, $_[0]);
    for my $word (@words) {
        return_next($word);
    }
    return;  # 必須以空 return 結束
$$;

SELECT * FROM perl_split_words('the quick brown fox');

# Ubuntu
sudo apt install postgresql-16-pgv8

# 啟用
psql -c "CREATE EXTENSION plv8;"

CREATE OR REPLACE FUNCTION js_hello(name text)
RETURNS text
LANGUAGE plv8
AS $$
    return `Hello, ${name}!`;
$$;

-- JavaScript 物件操作
CREATE OR REPLACE FUNCTION js_parse_config(config_str text)
RETURNS jsonb
LANGUAGE plv8
AS $$
    const config = JSON.parse(config_str);
    const defaults = { timeout: 30, retry: 3, debug: false };
    return Object.assign({}, defaults, config);
$$;

-- 複雜 JSON 扁平化
CREATE OR REPLACE FUNCTION js_flatten_json(nested jsonb, prefix text DEFAULT '')
RETURNS jsonb
LANGUAGE plv8
AS $$
    function flatten(obj, prefix, result) {
        result = result || {};
        for (let key in obj) {
            if (!obj.hasOwnProperty(key)) continue;
            const fullKey = prefix ? `${prefix}.${key}` : key;
            const val = obj[key];
            if (val !== null && typeof val === 'object' && !Array.isArray(val)) {
                flatten(val, fullKey, result);
            } else {
                result[fullKey] = val;
            }
        }
        return result;
    }
    return flatten(nested, prefix || '');
$$;

SELECT js_flatten_json(
    '{"user": {"name": "Alice", "addr": {"city": "Taipei"}}}'
);
-- 輸出：{"user.name": "Alice", "user.addr.city": "Taipei"}

CREATE OR REPLACE FUNCTION js_aggregate_sales(product_id integer)
RETURNS jsonb
LANGUAGE plv8
AS $$
    const rows = plv8.execute(
        'SELECT order_date, quantity, unit_price FROM order_items WHERE product_id = $1',
        [product_id]
    );

    const summary = rows.reduce((acc, row) => {
        const month = row.order_date.toISOString().substring(0, 7);
        if (!acc[month]) acc[month] = { quantity: 0, revenue: 0 };
        acc[month].quantity += row.quantity;
        acc[month].revenue += row.quantity * row.unit_price;
        return acc;
    }, {});

    return summary;
$$;

CREATE EXTENSION pltcl;

CREATE OR REPLACE FUNCTION tcl_hello(name text)
RETURNS text
LANGUAGE pltcl
AS $$
    return "Hello, $1!"
$$;

CREATE OR REPLACE FUNCTION tcl_word_count(sentence text)
RETURNS integer
LANGUAGE pltcl
AS $$
    set words [split $1]
    return [llength $words]
$$;