-- Test SQL file for Code Context Manager
-- This file contains various SQL constructs to test parsing
-- Create a simple table
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create a view
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE created_at > '2023-01-01';
-- Create a function
CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
BEGIN
RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE plpgsql;
-- Create a procedure
CREATE PROCEDURE update_user_email(
user_id INTEGER,
new_email VARCHAR(100)
)
AS $$
BEGIN
UPDATE users
SET email = new_email
WHERE id = user_id;
END;
$$ LANGUAGE plpgsql;
-- A complex SELECT query
SELECT
u.username,
u.email,
COUNT(o.id) as order_count,
SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2023-01-01'
GROUP BY u.id, u.username, u.email
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC;
