An experimental web framework where PostgreSQL does everything: routing, HTML, file storage, jobs and sessions.
natural_sql.sql · 18.5 KB · 597 lines
| 1 | -- Natural Language SQL Parser for PostgreSQL |
| 2 | -- Converts English-like queries to SQL and executes them |
| 3 | |
| 4 | -- Add to your PostBlob database |
| 5 | -- psql postblob < natural_sql.sql |
| 6 | |
| 7 | -- ============================================= |
| 8 | -- NATURAL LANGUAGE QUERY PARSER |
| 9 | -- ============================================= |
| 10 | |
| 11 | CREATE OR REPLACE FUNCTION natural_query(query_text text) |
| 12 | RETURNS TABLE(result jsonb) AS $$ |
| 13 | import re |
| 14 | import json |
| 15 | |
| 16 | def parse_natural_query(text): |
| 17 | """ |
| 18 | Parse natural language queries into SQL |
| 19 | |
| 20 | Examples: |
| 21 | - "get all users" |
| 22 | - "show me users where name is john" |
| 23 | - "find products with price greater than 100" |
| 24 | - "give me the first 5 orders from yesterday" |
| 25 | - "count users who signed up this week" |
| 26 | - "delete expired sessions" |
| 27 | - "update user dan set status to active" |
| 28 | """ |
| 29 | |
| 30 | # Normalize the text |
| 31 | text = text.lower().strip() |
| 32 | text = re.sub(r'\s+', ' ', text) # Multiple spaces to single |
| 33 | |
| 34 | # Common word replacements |
| 35 | replacements = { |
| 36 | 'get all': 'SELECT * FROM', |
| 37 | 'get': 'SELECT', |
| 38 | 'show me': 'SELECT * FROM', |
| 39 | 'show': 'SELECT', |
| 40 | 'find': 'SELECT * FROM', |
| 41 | 'give me': 'SELECT', |
| 42 | 'fetch': 'SELECT * FROM', |
| 43 | 'list': 'SELECT * FROM', |
| 44 | 'display': 'SELECT * FROM', |
| 45 | |
| 46 | # Conditions |
| 47 | 'with': 'WHERE', |
| 48 | 'having': 'WHERE', |
| 49 | 'that have': 'WHERE', |
| 50 | 'who have': 'WHERE', |
| 51 | 'which have': 'WHERE', |
| 52 | 'that has': 'WHERE', |
| 53 | 'who has': 'WHERE', |
| 54 | 'which has': 'WHERE', |
| 55 | |
| 56 | # Operators |
| 57 | 'equals to': '=', |
| 58 | 'equal to': '=', |
| 59 | 'equals': '=', |
| 60 | 'is': '=', |
| 61 | 'are': '=', |
| 62 | 'greater than': '>', |
| 63 | 'more than': '>', |
| 64 | 'above': '>', |
| 65 | 'less than': '<', |
| 66 | 'fewer than': '<', |
| 67 | 'below': '<', |
| 68 | 'at least': '>=', |
| 69 | 'at most': '<=', |
| 70 | 'contains': 'LIKE', |
| 71 | 'includes': 'LIKE', |
| 72 | 'starts with': 'LIKE', |
| 73 | 'ends with': 'LIKE', |
| 74 | 'between': 'BETWEEN', |
| 75 | |
| 76 | # Logical |
| 77 | 'and': 'AND', |
| 78 | 'or': 'OR', |
| 79 | 'not': 'NOT', |
| 80 | 'isnt': 'IS NOT', |
| 81 | "isn't": 'IS NOT', |
| 82 | 'is not': 'IS NOT', |
| 83 | |
| 84 | # Aggregates |
| 85 | 'count': 'COUNT(*)', |
| 86 | 'sum of': 'SUM', |
| 87 | 'total': 'SUM', |
| 88 | 'average': 'AVG', |
| 89 | 'mean': 'AVG', |
| 90 | 'maximum': 'MAX', |
| 91 | 'minimum': 'MIN', |
| 92 | 'biggest': 'MAX', |
| 93 | 'smallest': 'MIN', |
| 94 | 'largest': 'MAX', |
| 95 | |
| 96 | # Ordering |
| 97 | 'sorted by': 'ORDER BY', |
| 98 | 'ordered by': 'ORDER BY', |
| 99 | 'sort by': 'ORDER BY', |
| 100 | 'ascending': 'ASC', |
| 101 | 'descending': 'DESC', |
| 102 | 'newest first': 'ORDER BY created_at DESC', |
| 103 | 'oldest first': 'ORDER BY created_at ASC', |
| 104 | 'alphabetically': 'ORDER BY', |
| 105 | |
| 106 | # Limits |
| 107 | 'first': 'LIMIT', |
| 108 | 'top': 'LIMIT', |
| 109 | 'last': 'ORDER BY id DESC LIMIT', |
| 110 | 'bottom': 'ORDER BY id ASC LIMIT', |
| 111 | |
| 112 | # Time |
| 113 | 'today': "date_trunc('day', now())", |
| 114 | 'yesterday': "date_trunc('day', now() - interval '1 day')", |
| 115 | 'this week': "date_trunc('week', now())", |
| 116 | 'last week': "date_trunc('week', now() - interval '1 week')", |
| 117 | 'this month': "date_trunc('month', now())", |
| 118 | 'last month': "date_trunc('month', now() - interval '1 month')", |
| 119 | 'this year': "date_trunc('year', now())", |
| 120 | |
| 121 | # Tables (add your tables here) |
| 122 | 'users': 'users', |
| 123 | 'user': 'users', |
| 124 | 'people': 'users', |
| 125 | 'person': 'users', |
| 126 | 'files': 'blobs', |
| 127 | 'file': 'blobs', |
| 128 | 'blob': 'blobs', |
| 129 | 'blobs': 'blobs', |
| 130 | 'jobs': 'jobs', |
| 131 | 'job': 'jobs', |
| 132 | 'tasks': 'jobs', |
| 133 | 'task': 'jobs', |
| 134 | 'errors': 'exceptions', |
| 135 | 'error': 'exceptions', |
| 136 | 'exceptions': 'exceptions', |
| 137 | 'exception': 'exceptions', |
| 138 | 'session': 'sessions', |
| 139 | 'sessions': 'sessions', |
| 140 | } |
| 141 | |
| 142 | # Build SQL query |
| 143 | sql = text |
| 144 | |
| 145 | # Apply replacements |
| 146 | for eng, sql_term in replacements.items(): |
| 147 | sql = re.sub(r'\b' + eng + r'\b', sql_term, sql, flags=re.IGNORECASE) |
| 148 | |
| 149 | # Handle specific patterns |
| 150 | |
| 151 | # Pattern: "users named john" -> "users WHERE name = 'john'" |
| 152 | sql = re.sub(r'(\w+) named (\w+)', r"\1 WHERE name = '\2'", sql) |
| 153 | |
| 154 | # Pattern: "users with name john" -> "users WHERE name = 'john'" |
| 155 | sql = re.sub(r'WHERE (\w+) (\w+)', r"WHERE \1 = '\2'", sql) |
| 156 | |
| 157 | # Pattern: "5 users" -> "users LIMIT 5" |
| 158 | sql = re.sub(r'(\d+) (\w+)', r'\2 LIMIT \1', sql) |
| 159 | |
| 160 | # Pattern: "count of users" -> "COUNT(*) FROM users" |
| 161 | sql = re.sub(r'COUNT\(\*\) of (\w+)', r'COUNT(*) FROM \1', sql) |
| 162 | |
| 163 | # Pattern: "sum salary from employees" -> "SUM(salary) FROM employees" |
| 164 | sql = re.sub(r'SUM (\w+) from (\w+)', r'SUM(\1) FROM \2', sql) |
| 165 | |
| 166 | # Add SELECT if missing |
| 167 | if not any(sql.startswith(cmd) for cmd in ['SELECT', 'INSERT', 'UPDATE', 'DELETE']): |
| 168 | if 'FROM' in sql: |
| 169 | sql = 'SELECT * ' + sql |
| 170 | else: |
| 171 | # Assume first word is table name |
| 172 | parts = sql.split(' ', 1) |
| 173 | if len(parts) == 1: |
| 174 | sql = f'SELECT * FROM {parts[0]}' |
| 175 | else: |
| 176 | sql = f'SELECT * FROM {sql}' |
| 177 | |
| 178 | # Handle LIKE patterns |
| 179 | sql = re.sub(r"LIKE '(\w+)'", r"LIKE '%\1%'", sql) |
| 180 | sql = re.sub(r"LIKE '(\w+)%'", r"LIKE '\1%'", sql) |
| 181 | sql = re.sub(r"LIKE '%(\w+)'", r"LIKE '%\1'", sql) |
| 182 | |
| 183 | # Clean up extra spaces |
| 184 | sql = re.sub(r'\s+', ' ', sql).strip() |
| 185 | |
| 186 | # Add semicolon if missing |
| 187 | if not sql.endswith(';'): |
| 188 | sql += ';' |
| 189 | |
| 190 | return sql |
| 191 | |
| 192 | def execute_parsed_query(sql): |
| 193 | """Execute the parsed SQL and return results""" |
| 194 | try: |
| 195 | # Log the generated SQL for debugging |
| 196 | plpy.notice(f"Generated SQL: {sql}") |
| 197 | |
| 198 | # Execute the query |
| 199 | result = plpy.execute(sql) |
| 200 | |
| 201 | # Format results as JSON |
| 202 | if result: |
| 203 | return [dict(row) for row in result] |
| 204 | else: |
| 205 | return {'message': 'Query executed successfully', 'affected_rows': result.nrows() if hasattr(result, 'nrows') else 0} |
| 206 | |
| 207 | except Exception as e: |
| 208 | return {'error': str(e), 'sql': sql} |
| 209 | |
| 210 | # Parse and execute |
| 211 | sql = parse_natural_query(query_text) |
| 212 | results = execute_parsed_query(sql) |
| 213 | |
| 214 | # Return as JSON rows |
| 215 | if isinstance(results, list): |
| 216 | for row in results: |
| 217 | yield (json.dumps(row),) |
| 218 | else: |
| 219 | yield (json.dumps(results),) |
| 220 | |
| 221 | $$ LANGUAGE plpython3u; |
| 222 | |
| 223 | -- ============================================= |
| 224 | -- SIMPLER VERSION - PATTERN MATCHING |
| 225 | -- ============================================= |
| 226 | |
| 227 | CREATE OR REPLACE FUNCTION ask(question text) |
| 228 | RETURNS jsonb AS $$ |
| 229 | DECLARE |
| 230 | sql_query text; |
| 231 | result jsonb; |
| 232 | BEGIN |
| 233 | -- Simple pattern matching for common queries |
| 234 | sql_query := CASE |
| 235 | -- "show users" |
| 236 | WHEN question ~* '^show\s+users?$' THEN |
| 237 | 'SELECT * FROM users' |
| 238 | |
| 239 | -- "count users" |
| 240 | WHEN question ~* '^count\s+users?$' THEN |
| 241 | 'SELECT COUNT(*) as count FROM users' |
| 242 | |
| 243 | -- "show user dan" |
| 244 | WHEN question ~* '^show\s+user\s+(\w+)$' THEN |
| 245 | format('SELECT * FROM users WHERE username = %L', |
| 246 | (regexp_match(question, 'show\s+user\s+(\w+)', 'i'))[1]) |
| 247 | |
| 248 | -- "show 5 users" |
| 249 | WHEN question ~* '^show\s+(\d+)\s+users?$' THEN |
| 250 | format('SELECT * FROM users LIMIT %s', |
| 251 | (regexp_match(question, 'show\s+(\d+)', 'i'))[1]) |
| 252 | |
| 253 | -- "users where age > 25" |
| 254 | WHEN question ~* '^users?\s+where\s+(.+)$' THEN |
| 255 | format('SELECT * FROM users WHERE %s', |
| 256 | (regexp_match(question, 'where\s+(.+)$', 'i'))[1]) |
| 257 | |
| 258 | -- "delete old sessions" |
| 259 | WHEN question ~* '^delete\s+old\s+sessions?$' THEN |
| 260 | 'DELETE FROM sessions WHERE expires_at < now()' |
| 261 | |
| 262 | -- "biggest file" |
| 263 | WHEN question ~* '^biggest\s+file$' THEN |
| 264 | 'SELECT * FROM blobs ORDER BY size DESC LIMIT 1' |
| 265 | |
| 266 | -- "newest jobs" |
| 267 | WHEN question ~* '^newest\s+jobs?$' THEN |
| 268 | 'SELECT * FROM jobs ORDER BY created_at DESC LIMIT 10' |
| 269 | |
| 270 | -- "failed jobs" |
| 271 | WHEN question ~* '^failed\s+jobs?$' THEN |
| 272 | 'SELECT * FROM jobs WHERE status = ''failed''' |
| 273 | |
| 274 | ELSE |
| 275 | NULL |
| 276 | END; |
| 277 | |
| 278 | IF sql_query IS NULL THEN |
| 279 | RETURN jsonb_build_object( |
| 280 | 'error', 'Could not understand query', |
| 281 | 'original', question, |
| 282 | 'hint', 'Try: "show users", "count jobs", "show user dan", etc.' |
| 283 | ); |
| 284 | END IF; |
| 285 | |
| 286 | -- Execute the query |
| 287 | BEGIN |
| 288 | EXECUTE 'SELECT jsonb_agg(row_to_json(t)) FROM (' || sql_query || ') t' INTO result; |
| 289 | |
| 290 | IF result IS NULL THEN |
| 291 | result := '[]'::jsonb; |
| 292 | END IF; |
| 293 | |
| 294 | RETURN jsonb_build_object( |
| 295 | 'success', true, |
| 296 | 'sql', sql_query, |
| 297 | 'data', result |
| 298 | ); |
| 299 | EXCEPTION WHEN OTHERS THEN |
| 300 | RETURN jsonb_build_object( |
| 301 | 'error', SQLERRM, |
| 302 | 'sql', sql_query, |
| 303 | 'original', question |
| 304 | ); |
| 305 | END; |
| 306 | END; |
| 307 | $$ LANGUAGE plpgsql; |
| 308 | |
| 309 | -- ============================================= |
| 310 | -- ADVANCED ENGLISH-LIKE SYNTAX |
| 311 | -- ============================================= |
| 312 | |
| 313 | CREATE OR REPLACE FUNCTION english_sql(command text) |
| 314 | RETURNS jsonb AS $$ |
| 315 | import re |
| 316 | import json |
| 317 | |
| 318 | class EnglishSQLParser: |
| 319 | def __init__(self): |
| 320 | self.patterns = [ |
| 321 | # "from users get username and email where age exceeds 21" |
| 322 | (r"from (\w+) get ([\w\s,]+) where (.*)", self.parse_from_get_where), |
| 323 | |
| 324 | # "in the users table find all records where name contains john" |
| 325 | (r"in the (\w+) table find (.*) where (.*)", self.parse_in_table_find), |
| 326 | |
| 327 | # "grab the username and uuid from users for username dan" |
| 328 | (r"grab the ([\w\s,]+) from (\w+) for (\w+) (.*)", self.parse_grab_from_for), |
| 329 | |
| 330 | # "pull all data about dan from the users collection" |
| 331 | (r"pull (.*) about (\w+) from the (\w+)", self.parse_pull_about_from), |
| 332 | |
| 333 | # "how many users have status active" |
| 334 | (r"how many (\w+) have (\w+) (.*)", self.parse_how_many_have), |
| 335 | |
| 336 | # "what is the total size of all blobs" |
| 337 | (r"what is the (.*) of all (\w+)", self.parse_what_is_aggregate), |
| 338 | |
| 339 | # "store username:john email:john@example.com in users" |
| 340 | (r"store (.*) in (\w+)", self.parse_store_in), |
| 341 | |
| 342 | # "change status to active for user dan" |
| 343 | (r"change (\w+) to (.*) for (\w+) (.*)", self.parse_change_to_for), |
| 344 | |
| 345 | # "remove all expired items from cache" |
| 346 | (r"remove all (.*) from (\w+)", self.parse_remove_from), |
| 347 | ] |
| 348 | |
| 349 | def parse_from_get_where(self, match): |
| 350 | table = match.group(1) |
| 351 | columns = match.group(2).replace(' and ', ', ').replace(' ', '') |
| 352 | condition = self.parse_condition(match.group(3)) |
| 353 | return f"SELECT {columns} FROM {table} WHERE {condition}" |
| 354 | |
| 355 | def parse_in_table_find(self, match): |
| 356 | table = match.group(1) |
| 357 | what = match.group(2) |
| 358 | condition = self.parse_condition(match.group(3)) |
| 359 | |
| 360 | if 'all' in what: |
| 361 | columns = '*' |
| 362 | else: |
| 363 | columns = what.replace('records', '*').replace('rows', '*') |
| 364 | |
| 365 | return f"SELECT {columns} FROM {table} WHERE {condition}" |
| 366 | |
| 367 | def parse_grab_from_for(self, match): |
| 368 | columns = match.group(1).replace(' and ', ', ').replace(' ', '') |
| 369 | table = match.group(2) |
| 370 | field = match.group(3) |
| 371 | value = match.group(4).strip() |
| 372 | |
| 373 | # Handle quotes if not present |
| 374 | if not (value.startswith("'") or value.startswith('"')): |
| 375 | value = f"'{value}'" |
| 376 | |
| 377 | return f"SELECT {columns} FROM {table} WHERE {field} = {value}" |
| 378 | |
| 379 | def parse_pull_about_from(self, match): |
| 380 | what = match.group(1) |
| 381 | identifier = match.group(2) |
| 382 | table = match.group(3).replace(' collection', '').replace(' table', '') |
| 383 | |
| 384 | columns = '*' if 'all' in what else what |
| 385 | |
| 386 | # Guess the ID field |
| 387 | id_field = 'username' if table == 'users' else 'name' |
| 388 | |
| 389 | return f"SELECT {columns} FROM {table} WHERE {id_field} = '{identifier}'" |
| 390 | |
| 391 | def parse_how_many_have(self, match): |
| 392 | table = match.group(1) |
| 393 | field = match.group(2) |
| 394 | value = match.group(3).strip() |
| 395 | |
| 396 | if not (value.startswith("'") or value.startswith('"')): |
| 397 | value = f"'{value}'" |
| 398 | |
| 399 | return f"SELECT COUNT(*) as count FROM {table} WHERE {field} = {value}" |
| 400 | |
| 401 | def parse_what_is_aggregate(self, match): |
| 402 | aggregate = match.group(1) |
| 403 | table = match.group(2) |
| 404 | |
| 405 | if 'total size' in aggregate: |
| 406 | return f"SELECT SUM(size) as total_size FROM {table}" |
| 407 | elif 'count' in aggregate: |
| 408 | return f"SELECT COUNT(*) as count FROM {table}" |
| 409 | elif 'average' in aggregate or 'mean' in aggregate: |
| 410 | field = aggregate.split()[-1] |
| 411 | return f"SELECT AVG({field}) as average FROM {table}" |
| 412 | else: |
| 413 | return f"SELECT * FROM {table}" |
| 414 | |
| 415 | def parse_store_in(self, match): |
| 416 | data = match.group(1) |
| 417 | table = match.group(2) |
| 418 | |
| 419 | # Parse key:value pairs |
| 420 | pairs = re.findall(r'(\w+):([^\s]+)', data) |
| 421 | columns = ', '.join([p[0] for p in pairs]) |
| 422 | values = ', '.join([f"'{p[1]}'" if not p[1].isdigit() else p[1] for p in pairs]) |
| 423 | |
| 424 | return f"INSERT INTO {table} ({columns}) VALUES ({values})" |
| 425 | |
| 426 | def parse_change_to_for(self, match): |
| 427 | field = match.group(1) |
| 428 | value = match.group(2) |
| 429 | entity_type = match.group(3) |
| 430 | identifier = match.group(4).strip() |
| 431 | |
| 432 | # Map entity type to table |
| 433 | table = entity_type + 's' if not entity_type.endswith('s') else entity_type |
| 434 | id_field = 'username' if table == 'users' else 'id' |
| 435 | |
| 436 | if not (value.startswith("'") or value.startswith('"')) and not value.isdigit(): |
| 437 | value = f"'{value}'" |
| 438 | |
| 439 | return f"UPDATE {table} SET {field} = {value} WHERE {id_field} = '{identifier}'" |
| 440 | |
| 441 | def parse_remove_from(self, match): |
| 442 | what = match.group(1) |
| 443 | table = match.group(2) |
| 444 | |
| 445 | if 'expired' in what: |
| 446 | return f"DELETE FROM {table} WHERE expires_at < now()" |
| 447 | elif 'old' in what: |
| 448 | return f"DELETE FROM {table} WHERE created_at < now() - interval '30 days'" |
| 449 | else: |
| 450 | condition = self.parse_condition(what) |
| 451 | return f"DELETE FROM {table} WHERE {condition}" |
| 452 | |
| 453 | def parse_condition(self, text): |
| 454 | """Parse condition text into SQL WHERE clause""" |
| 455 | text = text.strip() |
| 456 | |
| 457 | # Replace English operators |
| 458 | text = re.sub(r'\bexceeds\b', '>', text) |
| 459 | text = re.sub(r'\bis greater than\b', '>', text) |
| 460 | text = re.sub(r'\bis less than\b', '<', text) |
| 461 | text = re.sub(r'\bcontains\b', 'LIKE', text) |
| 462 | text = re.sub(r'\bstarts with\b', 'LIKE', text) |
| 463 | text = re.sub(r'\bends with\b', 'LIKE', text) |
| 464 | text = re.sub(r'\bis\b', '=', text) |
| 465 | text = re.sub(r'\bequals\b', '=', text) |
| 466 | |
| 467 | # Handle LIKE patterns |
| 468 | if 'LIKE' in text: |
| 469 | # Extract the value after LIKE |
| 470 | parts = text.split('LIKE') |
| 471 | if len(parts) == 2: |
| 472 | value = parts[1].strip() |
| 473 | if not (value.startswith("'") or value.startswith('"')): |
| 474 | value = f"'%{value}%'" |
| 475 | text = f"{parts[0].strip()} LIKE {value}" |
| 476 | |
| 477 | # Add quotes to string values |
| 478 | words = text.split() |
| 479 | for i, word in enumerate(words): |
| 480 | if i > 0 and words[i-1] in ['=', '>', '<', '>=', '<=', '!=']: |
| 481 | if not (word.startswith("'") or word.startswith('"')) and not word.isdigit(): |
| 482 | words[i] = f"'{word}'" |
| 483 | |
| 484 | return ' '.join(words) |
| 485 | |
| 486 | def parse(self, command): |
| 487 | """Main parsing method""" |
| 488 | command = command.strip() |
| 489 | |
| 490 | # Try each pattern |
| 491 | for pattern, handler in self.patterns: |
| 492 | match = re.match(pattern, command, re.IGNORECASE) |
| 493 | if match: |
| 494 | return handler(match) |
| 495 | |
| 496 | # Fallback to simple interpretation |
| 497 | return self.simple_parse(command) |
| 498 | |
| 499 | def simple_parse(self, command): |
| 500 | """Simple fallback parser""" |
| 501 | command_lower = command.lower() |
| 502 | |
| 503 | if command_lower.startswith('show'): |
| 504 | parts = command_lower.replace('show', '').strip().split() |
| 505 | if parts: |
| 506 | table = parts[0] |
| 507 | return f"SELECT * FROM {table}" |
| 508 | |
| 509 | elif command_lower.startswith('count'): |
| 510 | parts = command_lower.replace('count', '').strip().split() |
| 511 | if parts: |
| 512 | table = parts[0] |
| 513 | return f"SELECT COUNT(*) as count FROM {table}" |
| 514 | |
| 515 | return None |
| 516 | |
| 517 | # Parse the command |
| 518 | parser = EnglishSQLParser() |
| 519 | sql = parser.parse(command) |
| 520 | |
| 521 | if sql is None: |
| 522 | return json.dumps({ |
| 523 | 'error': 'Could not parse command', |
| 524 | 'command': command, |
| 525 | 'hint': 'Try: "from users get username where age exceeds 21"' |
| 526 | }) |
| 527 | |
| 528 | try: |
| 529 | # Execute the SQL |
| 530 | plpy.notice(f"Executed SQL: {sql}") |
| 531 | |
| 532 | if sql.upper().startswith('SELECT'): |
| 533 | result = plpy.execute(sql) |
| 534 | data = [dict(row) for row in result] |
| 535 | return json.dumps({ |
| 536 | 'success': True, |
| 537 | 'sql': sql, |
| 538 | 'data': data, |
| 539 | 'count': len(data) |
| 540 | }) |
| 541 | else: |
| 542 | # INSERT, UPDATE, DELETE |
| 543 | result = plpy.execute(sql) |
| 544 | return json.dumps({ |
| 545 | 'success': True, |
| 546 | 'sql': sql, |
| 547 | 'affected_rows': result.nrows() if hasattr(result, 'nrows') else 0 |
| 548 | }) |
| 549 | |
| 550 | except Exception as e: |
| 551 | return json.dumps({ |
| 552 | 'error': str(e), |
| 553 | 'sql': sql, |
| 554 | 'command': command |
| 555 | }) |
| 556 | |
| 557 | $$ LANGUAGE plpython3u; |
| 558 | |
| 559 | -- ============================================= |
| 560 | -- EXAMPLE USAGE |
| 561 | -- ============================================= |
| 562 | |
| 563 | /* |
| 564 | -- Simple queries: |
| 565 | SELECT * FROM natural_query('show all users'); |
| 566 | SELECT * FROM natural_query('get users where name is john'); |
| 567 | SELECT * FROM natural_query('find the first 5 blobs'); |
| 568 | SELECT * FROM natural_query('count jobs that are failed'); |
| 569 | |
| 570 | -- Pattern matching: |
| 571 | SELECT ask('show users'); |
| 572 | SELECT ask('count users'); |
| 573 | SELECT ask('show user dan'); |
| 574 | SELECT ask('failed jobs'); |
| 575 | |
| 576 | -- English-like syntax: |
| 577 | SELECT english_sql('from users get username and email where age exceeds 21'); |
| 578 | SELECT english_sql('grab the username and uuid from users for username dan'); |
| 579 | SELECT english_sql('in the users table find all records where status is active'); |
| 580 | SELECT english_sql('how many users have status active'); |
| 581 | SELECT english_sql('what is the total size of all blobs'); |
| 582 | SELECT english_sql('remove all expired items from cache'); |
| 583 | */ |
| 584 | |
| 585 | -- Create a helper view for testing |
| 586 | CREATE OR REPLACE VIEW natural_sql_examples AS |
| 587 | SELECT |
| 588 | 'natural_query(''show all users'')' as example, |
| 589 | 'Lists all users' as description |
| 590 | UNION ALL |
| 591 | SELECT |
| 592 | 'ask(''show user dan'')' as example, |
| 593 | 'Show specific user' as description |
| 594 | UNION ALL |
| 595 | SELECT |
| 596 | 'english_sql(''from users get username and email where age exceeds 21'')' as example, |
| 597 | 'English-like syntax' as description; |