askrobots / postblob Public

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

0 downloads

postblob/helper_functions.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
helper_functions.sql · 8.3 KB · 322 lines
1-- Helper functions for web development (like early PHP)
2-- These provide HTML encoding, URL encoding/decoding and other web utilities
3
4-- HTML encode function (like PHP's htmlspecialchars)
5CREATE OR REPLACE FUNCTION html_encode(input text)
6RETURNS text AS $$
7BEGIN
8 IF input IS NULL THEN
9 RETURN NULL;
10 END IF;
11
12 -- Replace HTML special characters
13 RETURN replace(
14 replace(
15 replace(
16 replace(
17 replace(input, '&', '&'),
18 '<', '&lt;'),
19 '>', '&gt;'),
20 '"', '&quot;'),
21 '''', '&#39;');
22END;
23$$ LANGUAGE plpgsql IMMUTABLE;
24
25-- HTML decode function
26CREATE OR REPLACE FUNCTION html_decode(input text)
27RETURNS text AS $$
28BEGIN
29 IF input IS NULL THEN
30 RETURN NULL;
31 END IF;
32
33 -- Replace HTML entities back to characters
34 RETURN replace(
35 replace(
36 replace(
37 replace(
38 replace(input, '&lt;', '<'),
39 '&gt;', '>'),
40 '&quot;', '"'),
41 '&#39;', ''''),
42 '&amp;', '&');
43END;
44$$ LANGUAGE plpgsql IMMUTABLE;
45
46-- URL encode function (like PHP's urlencode)
47CREATE OR REPLACE FUNCTION url_encode(input text)
48RETURNS text AS $$
49DECLARE
50 result text := '';
51 char_code int;
52 i int;
53BEGIN
54 IF input IS NULL THEN
55 RETURN NULL;
56 END IF;
57
58 -- Encode each character
59 FOR i IN 1..length(input) LOOP
60 char_code := ascii(substring(input, i, 1));
61
62 -- Check if character needs encoding
63 IF (char_code >= 48 AND char_code <= 57) OR -- 0-9
64 (char_code >= 65 AND char_code <= 90) OR -- A-Z
65 (char_code >= 97 AND char_code <= 122) OR -- a-z
66 char_code IN (45, 46, 95, 126) THEN -- - . _ ~
67 -- Safe character, don't encode
68 result := result || substring(input, i, 1);
69 ELSIF char_code = 32 THEN
70 -- Space becomes +
71 result := result || '+';
72 ELSE
73 -- Encode as %HH
74 result := result || '%' || upper(to_hex(char_code));
75 END IF;
76 END LOOP;
77
78 RETURN result;
79END;
80$$ LANGUAGE plpgsql IMMUTABLE;
81
82-- URL decode function (like PHP's urldecode)
83CREATE OR REPLACE FUNCTION url_decode(input text)
84RETURNS text AS $$
85DECLARE
86 result text := '';
87 i int := 1;
88 len int;
89 hex_str text;
90BEGIN
91 IF input IS NULL THEN
92 RETURN NULL;
93 END IF;
94
95 len := length(input);
96
97 WHILE i <= len LOOP
98 IF substring(input, i, 1) = '%' AND i + 2 <= len THEN
99 -- Decode %HH
100 hex_str := substring(input, i + 1, 2);
101 IF hex_str ~ '^[0-9A-Fa-f]{2}$' THEN
102 result := result || chr(('x' || hex_str)::bit(8)::int);
103 i := i + 3;
104 ELSE
105 -- Invalid hex sequence, keep the %
106 result := result || '%';
107 i := i + 1;
108 END IF;
109 ELSIF substring(input, i, 1) = '+' THEN
110 -- + becomes space
111 result := result || ' ';
112 i := i + 1;
113 ELSE
114 -- Regular character
115 result := result || substring(input, i, 1);
116 i := i + 1;
117 END IF;
118 END LOOP;
119
120 RETURN result;
121END;
122$$ LANGUAGE plpgsql IMMUTABLE;
123
124-- Base64 encode function
125CREATE OR REPLACE FUNCTION base64_encode(input text)
126RETURNS text AS $$
127BEGIN
128 RETURN encode(input::bytea, 'base64');
129END;
130$$ LANGUAGE plpgsql IMMUTABLE;
131
132-- Base64 decode function
133CREATE OR REPLACE FUNCTION base64_decode(input text)
134RETURNS text AS $$
135BEGIN
136 RETURN convert_from(decode(input, 'base64'), 'UTF8');
137END;
138$$ LANGUAGE plpgsql IMMUTABLE;
139
140-- JSON escape function (for embedding data in JSON strings)
141CREATE OR REPLACE FUNCTION json_escape(input text)
142RETURNS text AS $$
143BEGIN
144 IF input IS NULL THEN
145 RETURN NULL;
146 END IF;
147
148 -- Use PostgreSQL's built-in JSON escaping
149 RETURN to_json(input)::text;
150END;
151$$ LANGUAGE plpgsql IMMUTABLE;
152
153-- Query string parse function (like PHP's parse_str)
154-- Returns a JSONB object from URL query string
155CREATE OR REPLACE FUNCTION parse_query_string(query_string text)
156RETURNS jsonb AS $$
157DECLARE
158 params jsonb := '{}'::jsonb;
159 pair text;
160 key text;
161 value text;
162 pos int;
163BEGIN
164 IF query_string IS NULL OR query_string = '' THEN
165 RETURN params;
166 END IF;
167
168 -- Remove leading ? if present
169 IF left(query_string, 1) = '?' THEN
170 query_string := substring(query_string, 2);
171 END IF;
172
173 -- Split by & and process each pair
174 FOREACH pair IN ARRAY string_to_array(query_string, '&') LOOP
175 pos := position('=' IN pair);
176 IF pos > 0 THEN
177 key := url_decode(substring(pair, 1, pos - 1));
178 value := url_decode(substring(pair, pos + 1));
179 ELSE
180 key := url_decode(pair);
181 value := '';
182 END IF;
183
184 -- Add to params
185 params := params || jsonb_build_object(key, value);
186 END LOOP;
187
188 RETURN params;
189END;
190$$ LANGUAGE plpgsql IMMUTABLE;
191
192-- Build query string from JSONB (like PHP's http_build_query)
193CREATE OR REPLACE FUNCTION build_query_string(params jsonb)
194RETURNS text AS $$
195DECLARE
196 result text := '';
197 key text;
198 value text;
199 first boolean := true;
200BEGIN
201 IF params IS NULL THEN
202 RETURN '';
203 END IF;
204
205 FOR key, value IN SELECT * FROM jsonb_each_text(params) LOOP
206 IF NOT first THEN
207 result := result || '&';
208 END IF;
209 result := result || url_encode(key) || '=' || url_encode(value);
210 first := false;
211 END LOOP;
212
213 RETURN result;
214END;
215$$ LANGUAGE plpgsql IMMUTABLE;
216
217-- Strip HTML tags (like PHP's strip_tags)
218CREATE OR REPLACE FUNCTION strip_tags(input text)
219RETURNS text AS $$
220BEGIN
221 IF input IS NULL THEN
222 RETURN NULL;
223 END IF;
224
225 -- Remove all HTML tags
226 RETURN regexp_replace(input, '<[^>]+>', '', 'g');
227END;
228$$ LANGUAGE plpgsql IMMUTABLE;
229
230-- Truncate text with ellipsis
231CREATE OR REPLACE FUNCTION truncate_text(input text, max_length int, ellipsis text DEFAULT '...')
232RETURNS text AS $$
233BEGIN
234 IF input IS NULL OR length(input) <= max_length THEN
235 RETURN input;
236 END IF;
237
238 RETURN left(input, max_length - length(ellipsis)) || ellipsis;
239END;
240$$ LANGUAGE plpgsql IMMUTABLE;
241
242-- Generate random string (useful for tokens, session IDs, etc.)
243CREATE OR REPLACE FUNCTION random_string(len int)
244RETURNS text AS $$
245DECLARE
246 chars text := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
247 result text := '';
248 i int;
249BEGIN
250 FOR i IN 1..len LOOP
251 result := result || substring(chars, floor(random() * length(chars) + 1)::int, 1);
252 END LOOP;
253
254 RETURN result;
255END;
256$$ LANGUAGE plpgsql;
257
258-- Slugify function (convert text to URL-friendly slug)
259CREATE OR REPLACE FUNCTION slugify(input text)
260RETURNS text AS $$
261BEGIN
262 IF input IS NULL THEN
263 RETURN NULL;
264 END IF;
265
266 RETURN lower(
267 regexp_replace(
268 regexp_replace(
269 regexp_replace(
270 input,
271 '[^a-zA-Z0-9\s-]', '', 'g' -- Remove special chars
272 ),
273 '\s+', '-', 'g' -- Replace spaces with hyphens
274 ),
275 '-+', '-', 'g' -- Replace multiple hyphens with single
276 )
277 );
278END;
279$$ LANGUAGE plpgsql IMMUTABLE;
280
281-- Email validation function
282CREATE OR REPLACE FUNCTION is_valid_email(email text)
283RETURNS boolean AS $$
284BEGIN
285 RETURN email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
286END;
287$$ LANGUAGE plpgsql IMMUTABLE;
288
289-- Get file extension from filename
290CREATE OR REPLACE FUNCTION get_file_extension(filename text)
291RETURNS text AS $$
292DECLARE
293 pos int;
294BEGIN
295 pos := position('.' IN reverse(filename));
296 IF pos > 0 THEN
297 RETURN lower(right(filename, pos - 1));
298 END IF;
299 RETURN '';
300END;
301$$ LANGUAGE plpgsql IMMUTABLE;
302
303-- Format bytes as human-readable size
304CREATE OR REPLACE FUNCTION format_bytes(bytes bigint)
305RETURNS text AS $$
306DECLARE
307 units text[] := ARRAY['B', 'KB', 'MB', 'GB', 'TB'];
308 unit_index int := 1;
309 size numeric := bytes;
310BEGIN
311 WHILE size >= 1024 AND unit_index < array_length(units, 1) LOOP
312 size := size / 1024;
313 unit_index := unit_index + 1;
314 END LOOP;
315
316 IF unit_index = 1 THEN
317 RETURN bytes || ' B';
318 ELSE
319 RETURN round(size, 2) || ' ' || units[unit_index];
320 END IF;
321END;
322$$ LANGUAGE plpgsql IMMUTABLE;