askrobots / postblob Public

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

0 downloads

postblob/simple_code_routes.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
simple_code_routes.sql · 2.9 KB · 98 lines
1-- Simplified code_routes
2CREATE OR REPLACE FUNCTION code_routes(
3 p_method text,
4 p_path text,
5 p_body jsonb DEFAULT '{}'::jsonb
6) RETURNS jsonb AS $$
7DECLARE
8 v_result jsonb;
9 v_file_path text;
10 v_content text;
11 v_crud_result jsonb;
12 v_query_start integer;
13BEGIN
14 -- Route: GET /code (Editor UI)
15 IF p_method = 'GET' AND p_path = '/code' THEN
16 SELECT content INTO v_content
17 FROM code_files
18 WHERE path = 'ui/code_editor.html';
19
20 RETURN jsonb_build_object(
21 'status', 200,
22 'headers', jsonb_build_object('Content-Type', 'text/html'),
23 'body', v_content,
24 'html', true
25 );
26
27 -- Route: GET /code/list
28 ELSIF p_method = 'GET' AND p_path = '/code/list' THEN
29 v_crud_result := code_crud('LIST', NULL);
30 RETURN jsonb_build_object(
31 'status', 200,
32 'body', v_crud_result
33 );
34
35 -- Route: GET /code/file
36 ELSIF p_method = 'GET' AND p_path LIKE '/code/file?%' THEN
37 -- Simple extraction: find position of 'path='
38 v_query_start := position('path=' in p_path);
39
40 IF v_query_start > 0 THEN
41 -- Extract everything after 'path='
42 v_file_path := substring(p_path from v_query_start + 5);
43
44 -- URL decode
45 v_file_path := replace(v_file_path, '%2F', '/');
46 v_file_path := replace(v_file_path, '%20', ' ');
47 v_file_path := replace(v_file_path, '+', ' ');
48
49 -- Remove any trailing parameters
50 IF position('&' in v_file_path) > 0 THEN
51 v_file_path := substring(v_file_path from 1 for position('&' in v_file_path) - 1);
52 END IF;
53 END IF;
54
55 -- Get the file
56 v_crud_result := code_crud('GET', v_file_path);
57
58 RETURN jsonb_build_object(
59 'status', 200,
60 'body', v_crud_result
61 );
62
63 -- Route: POST /code/save
64 ELSIF p_method = 'POST' AND p_path = '/code/save' THEN
65 v_crud_result := code_crud('PUT', p_body->>'path', p_body->>'content');
66 RETURN jsonb_build_object(
67 'status', 200,
68 'body', v_crud_result
69 );
70
71 -- Route: POST /code/execute
72 ELSIF p_method = 'POST' AND p_path = '/code/execute' THEN
73 v_crud_result := execute_code(
74 COALESCE(p_body->>'language', 'sql'),
75 p_body->>'code',
76 p_body->>'save_as'
77 );
78 RETURN jsonb_build_object(
79 'status', 200,
80 'body', v_crud_result
81 );
82
83 -- Route: POST /code/reload
84 ELSIF p_method = 'POST' AND p_path = '/code/reload' THEN
85 v_crud_result := hot_reload(p_body->>'path');
86 RETURN jsonb_build_object(
87 'status', 200,
88 'body', v_crud_result
89 );
90
91 ELSE
92 RETURN jsonb_build_object(
93 'status', 404,
94 'body', jsonb_build_object('error', 'Route not found', 'path', p_path)
95 );
96 END IF;
97END;
98$$ LANGUAGE plpgsql;