-- 创建计算斐波那契数列的函数
CREATE OR REPLACE FUNCTION fibonacci(n INTEGER)
RETURNS BIGINT AS $$
DECLARE
    a BIGINT := 0;
    b BIGINT := 1;
    temp BIGINT;
    i INTEGER;
BEGIN
    IF n <= 0 THEN
        RETURN 0;
    ELSIF n = 1 THEN
        RETURN 1;
    END IF;
    
    FOR i IN 2..n LOOP
        temp := a + b;
        a := b;
        b := temp;
    END LOOP;
    
    RETURN b;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

-- 使用函数
SELECT fibonacci(10);  -- 返回 55

-- 创建返回表类型的函数
CREATE OR REPLACE FUNCTION get_employee_stats(dept_id INTEGER)
RETURNS TABLE (
    employee_count INTEGER,
    avg_salary NUMERIC(10,2),
    max_salary NUMERIC(10,2),
    min_salary NUMERIC(10,2)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COUNT(*)::INTEGER,
        AVG(salary)::NUMERIC(10,2),
        MAX(salary)::NUMERIC(10,2),
        MIN(salary)::NUMERIC(10,2)
    FROM employees
    WHERE department_id = dept_id;
END;
$$ LANGUAGE plpgsql;

-- 使用函数
SELECT * FROM get_employee_stats(1);

-- 创建PL/Python扩展
CREATE EXTENSION plpython3u;

-- 或者使用plpythonu（Python 2，已逐渐淘汰）
-- CREATE EXTENSION plpythonu;

-- 创建情感分析函数
CREATE OR REPLACE FUNCTION analyze_sentiment(text_content TEXT)
RETURNS JSON AS $$
import json
from textblob import TextBlob

def analyze(text):
    blob = TextBlob(text)
    sentiment = blob.sentiment
    
    return {
        'polarity': float(sentiment.polarity),
        'subjectivity': float(sentiment.subjectivity),
        'sentiment': 'positive' if sentiment.polarity > 0 
                    else 'negative' if sentiment.polarity < 0 
                    else 'neutral'
    }

try:
    result = analyze(text_content)
    return json.dumps(result)
except Exception as e:
    return json.dumps({'error': str(e)})
$$ LANGUAGE plpython3u;

-- 使用函数
SELECT analyze_sentiment('PostgreSQL is an amazing database!');

-- 创建调用天气API的函数
CREATE OR REPLACE FUNCTION get_weather(city TEXT)
RETURNS JSON AS $$
import json
import urllib.request
import urllib.parse

def fetch_weather(city_name):
    api_key = 'your_api_key_here'
    url = f'http://api.openweathermap.org/data/2.5/weather'
    params = {
        'q': city_name,
        'appid': api_key,
        'units': 'metric'
    }
    
    query_string = urllib.parse.urlencode(params)
    full_url = f'{url}?{query_string}'
    
    with urllib.request.urlopen(full_url) as response:
        data = json.loads(response.read().decode())
    
    return {
        'city': data['name'],
        'temperature': data['main']['temp'],
        'humidity': data['main']['humidity'],
        'description': data['weather'][0]['description']
    }

try:
    result = fetch_weather(city)
    return json.dumps(result)
except Exception as e:
    return json.dumps({'error': str(e)})
$$ LANGUAGE plpython3u;

-- 创建复数类型
CREATE TYPE complex;

-- 创建输入输出函数
CREATE FUNCTION complex_in(cstring)
RETURNS complex
AS '$libdir/my_complex'
LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION complex_out(complex)
RETURNS cstring
AS '$libdir/my_complex'
LANGUAGE C IMMUTABLE STRICT;

-- 创建类型
CREATE TYPE complex (
    internallength = 16,
    input = complex_in,
    output = complex_out,
    alignment = double
);

-- 创建复数加法函数
CREATE FUNCTION complex_add(complex, complex)
RETURNS complex
AS '$libdir/my_complex'
LANGUAGE C IMMUTABLE STRICT;

-- 创建操作符
CREATE OPERATOR + (
    leftarg = complex,
    rightarg = complex,
    procedure = complex_add,
    commutator = +
);

-- 创建计算两点间距离的函数（使用Haversine公式）
CREATE OR REPLACE FUNCTION calculate_distance(
    lat1 FLOAT, lon1 FLOAT,
    lat2 FLOAT, lon2 FLOAT
)
RETURNS FLOAT AS $$
DECLARE
    R FLOAT := 6371; -- 地球半径（公里）
    dlat FLOAT;
    dlon FLOAT;
    a FLOAT;
    c FLOAT;
BEGIN
    -- 转换为弧度
    lat1 := radians(lat1);
    lon1 := radians(lon1);
    lat2 := radians(lat2);
    lon2 := radians(lon2);
    
    -- 计算差值
    dlat := lat2 - lat1;
    dlon := lon2 - lon1;
    
    -- Haversine公式
    a := sin(dlat/2)^2 + cos(lat1) * cos(lat2) * sin(dlon/2)^2;
    c := 2 * asin(sqrt(a));
    
    RETURN R * c;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 创建JSON Schema验证函数
CREATE OR REPLACE FUNCTION validate_json_schema(
    json_data JSONB,
    schema JSONB
)
RETURNS BOOLEAN AS $$
import jsonschema
from jsonschema import validate

try:
    # 将JSONB转换为Python字典
    data = json_data
    schema_dict = schema
    
    # 验证数据
    validate(instance=data, schema=schema_dict)
    return True
except jsonschema.exceptions.ValidationError as e:
    return False
except Exception as e:
    raise
$$ LANGUAGE plpython3u;