Functions and Triggers

-- Simple function
CREATE OR REPLACE FUNCTION get_user_post_count(user_id BIGINT)
RETURNS INTEGER AS $$
    SELECT COUNT(*)::INTEGER FROM posts WHERE user_id = $1 AND published = TRUE;
$$ LANGUAGE SQL STABLE;

-- Usage
SELECT username, get_user_post_count(id) FROM users;

-- PL/pgSQL function
CREATE OR REPLACE FUNCTION calculate_user_tier(user_id BIGINT)
RETURNS TEXT AS $$
DECLARE
    post_count INTEGER;
    total_views BIGINT;
BEGIN
    SELECT COUNT(*), COALESCE(SUM(views), 0)
    INTO post_count, total_views
    FROM posts
    WHERE posts.user_id = $1 AND published = TRUE;

    IF total_views > 100000 THEN
        RETURN 'platinum';
    ELSIF total_views > 10000 THEN
        RETURN 'gold';
    ELSIF post_count > 10 THEN
        RETURN 'silver';
    ELSE
        RETURN 'bronze';
    END IF;
END;
$$ LANGUAGE plpgsql STABLE;

-- Auto-update updated_at trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

-- Audit log trigger
CREATE TABLE audit_log (
    id          BIGSERIAL PRIMARY KEY,
    table_name  TEXT,
    operation   TEXT,
    old_data    JSONB,
    new_data    JSONB,
    changed_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log(table_name, operation, old_data, new_data)
    VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION audit_changes();