askrobots / postblob Public

An experimental web framework where PostgreSQL does everything: routing, HTML, file storage, jobs and sessions.

0 downloads

postblob/update_ask_function.sql
Download zip
Clone
Clone with git
git clone https://gitstable.com/git/askrobots/postblob.git
HTTPS
https://gitstable.com/git/askrobots/postblob.git
SSH
ssh://git@gitstable.com:2222/askrobots/postblob.git

Cloning needs no sign-in. Pushing over HTTPS asks for your username and an access token. SSH needs your key under SSH keys.

danq9 docs: Add screenshots to README ae44e69 1 year, 1 month ago History
update_ask_function.sql · 1.6 KB · 31 lines
1-- Update ask() function to support code-related queries
2
3UPDATE code_files
4SET 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),
11version = version + 1,
12updated_at = now()
13WHERE path = 'functions/ask.sql';
14
15-- Hot reload the ask function
16\o /dev/null
17SELECT content FROM code_files WHERE path = 'functions/ask.sql' \gset
18\o
19
20DROP FUNCTION IF EXISTS ask(text);
21
22DO $$
23DECLARE
24 v_sql text;
25BEGIN
26 SELECT content INTO v_sql FROM code_files WHERE path = 'functions/ask.sql';
27 EXECUTE v_sql;
28END $$;
29
30-- Return status
31SELECT 'Updated ask() function to support code queries!' as result;