Try It Yourself - SyntaxStudy Editor

HTML / CSS / JS

-- Stored procedure with transaction control
CREATE OR REPLACE PROCEDURE process_order_batch(batch_size INTEGER DEFAULT 100)
LANGUAGE plpgsql AS $$
DECLARE
  processed INTEGER := 0;
BEGIN
  LOOP
    UPDATE orders
      SET status = 'processing', started_at = NOW()
    WHERE id IN (
      SELECT id FROM orders
      WHERE status = 'pending'
      ORDER BY created_at
      LIMIT batch_size
    );

processed := processed + ROW_COUNT;
    COMMIT;  -- checkpoint after each batch

EXIT WHEN ROW_COUNT = 0;
  END LOOP;
  RAISE NOTICE 'Processed % orders', processed;
END;
$$;

CALL process_order_batch(50);

-- Trigger function — maintain audit log
CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_at, changed_by)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN row_to_json(OLD) END,
    CASE WHEN TG_OP IN ('UPDATE','INSERT') THEN row_to_json(NEW) END,
    NOW(),
    current_user
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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