An experimental web framework where PostgreSQL does everything: routing, HTML, file storage, jobs and sessions.
update_ask_function.sql · 1.6 KB · 31 lines
| 1 | -- Update ask() function to support code-related queries |
| 2 | |
| 3 | UPDATE code_files |
| 4 | SET content = regexp_replace( |
| 5 | content, |
| 6 | -- Find the ELSE clause before the error case |
| 7 | '(ELSE\s+-- Could not understand query)', |
| 8 | E'WHEN v_query ~* ''(show|list|view|get)\\s+(code|functions?|stored|procedures?|sql)'' THEN\n -- Show code files\n v_sql := ''SELECT path, language, version, updated_at FROM code_files WHERE is_active = true ORDER BY path LIMIT 20'';\n EXECUTE v_sql INTO v_result;\n RETURN jsonb_build_object(\n ''success'', true,\n ''sql'', v_sql,\n ''data'', v_result\n );\n \n WHEN v_query ~* ''(show|list|view|get)\\s+helpers?'' THEN\n -- Show helper functions\n v_sql := ''SELECT path, substring(content, 1, 100) as preview FROM code_files WHERE path LIKE ''''functions/%%'''' AND (path LIKE ''''%%encode%%'''' OR path LIKE ''''%%decode%%'''' OR path LIKE ''''%%slug%%'''' OR path LIKE ''''%%format%%'''') ORDER BY path'';\n EXECUTE v_sql INTO v_result;\n RETURN jsonb_build_object(\n ''success'', true,\n ''sql'', v_sql,\n ''data'', v_result\n );\n\n \\1', |
| 9 | 'g' |
| 10 | ), |
| 11 | version = version + 1, |
| 12 | updated_at = now() |
| 13 | WHERE path = 'functions/ask.sql'; |
| 14 | |
| 15 | -- Hot reload the ask function |
| 16 | \o /dev/null |
| 17 | SELECT content FROM code_files WHERE path = 'functions/ask.sql' \gset |
| 18 | \o |
| 19 | |
| 20 | DROP FUNCTION IF EXISTS ask(text); |
| 21 | |
| 22 | DO $$ |
| 23 | DECLARE |
| 24 | v_sql text; |
| 25 | BEGIN |
| 26 | SELECT content INTO v_sql FROM code_files WHERE path = 'functions/ask.sql'; |
| 27 | EXECUTE v_sql; |
| 28 | END $$; |
| 29 | |
| 30 | -- Return status |
| 31 | SELECT 'Updated ask() function to support code queries!' as result; |