sql

-- Simple function
CREATE OR REPLACE FUNCTION get_full_name(
  first_name VARCHAR,
  last_name VARCHAR
)
RETURNS VARCHAR AS $$

# Vulnerable: user input is concatenated directly into SQL.
email = params[:email]
password = params[:password]

sql = "SELECT * FROM users WHERE email = '#{email}' AND password_hash = '#{password}'"
user = ActiveRecord::Base.connection.execute(sql).first

-- EXPLAIN ANALYZE (actual execution statistics)
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'

-- Publisher: Send notification
NOTIFY new_order, 'Order #12345 created';

-- Subscriber: Listen for notifications
LISTEN new_order;

-- Logical backup with pg_dump
-- Single database
-- pg_dump -h localhost -U postgres -d mydb -F c -f mydb_backup.dump

-- All databases
-- pg_dumpall -h localhost -U postgres -f all_databases.sql

CREATE TABLE IF NOT EXISTS outbox_events (
  id BIGSERIAL PRIMARY KEY,
  event_name TEXT NOT NULL,
  dedupe_key TEXT NOT NULL UNIQUE,
  payload JSONB NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',

-- Prepared statements basics
-- PostgreSQL syntax
PREPARE get_user (INT) AS
SELECT id, username, email
FROM users
WHERE id = $1;

-- Install PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- Create table with geometry column
CREATE TABLE locations (
  id SERIAL PRIMARY KEY,

-- Pattern 1: Shared schema with tenant_id column

CREATE TABLE tenants (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(50) UNIQUE NOT NULL,

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

-- ROW_NUMBER: Unique sequential number
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as overall_rank,

-- Row-level locks with SELECT FOR UPDATE
BEGIN;

SELECT * FROM inventory
WHERE product_id = 123
FOR UPDATE;  -- Locks selected rows