Stored procedures and functions in PostgreSQL

-- Stored Procedure (PostgreSQL 11+)
CREATE OR REPLACE PROCEDURE process_monthly_billing()
LANGUAGE plpgsql
AS $$
DECLARE
  customer_record RECORD;
BEGIN
  FOR customer_record IN
    SELECT id, email, monthly_fee
    FROM customers
    WHERE status = 'active'
  LOOP
    -- Create invoice
    INSERT INTO invoices (customer_id, amount, due_date)
    VALUES (
      customer_record.id,
      customer_record.monthly_fee,
      CURRENT_DATE + INTERVAL '30 days'
    );

    -- Send email (log for now)
    INSERT INTO email_queue (recipient, subject)
    VALUES (
      customer_record.email,
      'Your monthly invoice'
    );

    COMMIT;  -- Commit each customer separately
  END LOOP;
END;
$$;

-- Call procedure
CALL process_monthly_billing();

-- Trigger function for auto-updating timestamps
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

-- Trigger for data validation
CREATE OR REPLACE FUNCTION validate_email()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Z|a-z]{2,}$' THEN
    RAISE EXCEPTION 'Invalid email format: %', NEW.email;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_email_format
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION validate_email();

-- Trigger for maintaining aggregate counts
CREATE OR REPLACE FUNCTION update_post_count()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE users
    SET posts_count = posts_count + 1
    WHERE id = NEW.user_id;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE users
    SET posts_count = posts_count - 1
    WHERE id = OLD.user_id;
    RETURN OLD;
  ELSIF TG_OP = 'UPDATE' THEN
    IF NEW.user_id != OLD.user_id THEN
      UPDATE users SET posts_count = posts_count - 1
      WHERE id = OLD.user_id;
      UPDATE users SET posts_count = posts_count + 1
      WHERE id = NEW.user_id;
    END IF;
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER maintain_post_count
AFTER INSERT OR UPDATE OR DELETE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_post_count();

-- Instead Of Trigger (for views)
CREATE VIEW user_summaries AS
SELECT
  u.id,
  u.name,
  COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name;

CREATE OR REPLACE FUNCTION update_user_summary()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE users
  SET name = NEW.name
  WHERE id = NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_user_via_view
INSTEAD OF UPDATE ON user_summaries
FOR EACH ROW
EXECUTE FUNCTION update_user_summary();

-- Conditional trigger
CREATE TRIGGER notify_high_value_order
AFTER INSERT ON orders
FOR EACH ROW
WHEN (NEW.total > 10000)
EXECUTE FUNCTION notify_sales_team();

-- Drop trigger
DROP TRIGGER IF EXISTS set_updated_at ON users;

-- Disable/Enable trigger
ALTER TABLE users DISABLE TRIGGER set_updated_at;
ALTER TABLE users ENABLE TRIGGER set_updated_at;