Skip to main content

dsl_knowledge/tables/
functions.rs

1//! SQL built-in function table. Append new functions with the `f!` macro.
2
3use crate::entry::{Entry, Kind, pg};
4use std::collections::HashMap;
5
6pub fn build() -> HashMap<&'static str, Entry> {
7  let mut m = HashMap::new();
8  macro_rules! f {
9    ($label:expr, $sig:expr, $doc:expr, $example:expr, $url:expr) => {
10      // Panic on a duplicate label rather than silently overwriting --
11      // a HashMap::insert collision here means a second entry for the
12      // same function was added without noticing the first, and
13      // whichever one lost would go permanently dead with no warning.
14      // See the 2026-08-18 dedup commit, which found and removed 346
15      // pre-existing duplicates this same way.
16      if let Some(prev) = m.insert(
17        $label,
18        Entry { label: $label, kind: Kind::Function, doc: $doc, signature: Some($sig), example: $example, url: $url },
19      ) {
20        panic!(
21          "duplicate dsl-knowledge function entry {:?}: new doc {:?} would silently replace existing doc {:?}",
22          $label, $doc, prev.doc
23        );
24      }
25    };
26  }
27
28  // Conditional
29  f!(
30    "coalesce",
31    "coalesce(a, b, ...) -> any",
32    "Return the first non-null argument.",
33    "SELECT coalesce(nickname, name, 'anonymous') FROM users;",
34    pg("functions-conditional.html")
35  );
36  f!(
37    "nullif",
38    "nullif(a, b) -> any",
39    "Return NULL when a = b, else a.",
40    "SELECT nullif(value, '') FROM t;",
41    pg("functions-conditional.html")
42  );
43  f!(
44    "greatest",
45    "greatest(a, b, ...) -> any",
46    "Largest non-null argument.",
47    "SELECT greatest(a, b, c) FROM t;",
48    pg("functions-conditional.html")
49  );
50  f!(
51    "least",
52    "least(a, b, ...) -> any",
53    "Smallest non-null argument.",
54    "SELECT least(a, b, c) FROM t;",
55    pg("functions-conditional.html")
56  );
57
58  // String
59
60  f!(
61    "substring",
62    "substring(text FROM start [FOR len]) -> text",
63    "Slice a string. 1-based.",
64    "SELECT substring(email FROM 1 FOR 3) FROM users;",
65    pg("functions-string.html")
66  );
67
68  // Date / time
69  f!(
70    "now",
71    "now() -> timestamptz",
72    "Current statement timestamp.",
73    "INSERT INTO events (at) VALUES (now());",
74    pg("functions-datetime.html#FUNCTIONS-DATETIME-CURRENT")
75  );
76
77  // -----------------------------------------------------------------------
78
79  f!("chr", "chr(int) -> text", "Character for a Unicode code point.", "SELECT chr(65);", pg("functions-string.html"));
80
81  // -----------------------------------------------------------------------
82
83  f!("exp", "exp(numeric) -> numeric", "e raised to argument.", "SELECT exp(1);", pg("functions-math.html"));
84  f!("ln", "ln(numeric) -> numeric", "Natural log.", "SELECT ln(2.718);", pg("functions-math.html"));
85
86  f!("pi", "pi() -> double", "3.14159...", "SELECT pi();", pg("functions-math.html"));
87
88  // -----------------------------------------------------------------------
89
90  f!(
91    "sha1",
92    "sha1(bytea) -> bytea",
93    "SHA-1 binary (pgcrypto).",
94    "SELECT encode(sha1('hi'), 'hex');",
95    pg("pgcrypto.html")
96  );
97
98  // -----------------------------------------------------------------------
99
100  f!(
101    "obj_description",
102    "obj_description(object_oid [, catalog_name text]) -> text",
103    "Comment attached to a database object (table, function, ...).",
104    "SELECT obj_description('users'::regclass);",
105    pg("functions-info.html#FUNCTIONS-INFO-COMMENT")
106  );
107  f!(
108    "col_description",
109    "col_description(table_oid, column_number) -> text",
110    "Comment on a specific column.",
111    "SELECT col_description('users'::regclass, 1);",
112    pg("functions-info.html#FUNCTIONS-INFO-COMMENT")
113  );
114  f!(
115    "shobj_description",
116    "shobj_description(object_oid, shared_catalog_name text) -> text",
117    "Comment on a shared (cluster-wide) object such as a role or database.",
118    "SELECT shobj_description(d.oid, 'pg_database') FROM pg_database d;",
119    pg("functions-info.html#FUNCTIONS-INFO-COMMENT")
120  );
121
122  f!(
123    "gen_random_bytes",
124    "gen_random_bytes(n int) -> bytea (pgcrypto)",
125    "Cryptographically strong random bytes. Requires the pgcrypto extension.",
126    "SELECT gen_random_bytes(16);",
127    pg("pgcrypto.html")
128  );
129
130  f!(
131    "normalize",
132    "normalize(text [, form NFC|NFD|NFKC|NFKD]) -> text",
133    "Unicode normalization (PG 13+). Default form is NFC.",
134    "SELECT normalize('café', NFC);",
135    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
136  );
137
138  f!(
139    "current_user",
140    "current_user -> name",
141    "Current SQL session user.",
142    "SELECT current_user;",
143    pg("functions-info.html")
144  );
145  f!(
146    "session_user",
147    "session_user -> name",
148    "Original session user (before SET ROLE).",
149    "SELECT session_user;",
150    pg("functions-info.html")
151  );
152  f!(
153    "current_database",
154    "current_database() -> name",
155    "Active database name.",
156    "SELECT current_database();",
157    pg("functions-info.html")
158  );
159  f!(
160    "current_schema",
161    "current_schema() -> name",
162    "First entry in search_path.",
163    "SELECT current_schema();",
164    pg("functions-info.html")
165  );
166  f!("version", "version() -> text", "Postgres server version banner.", "SELECT version();", pg("functions-info.html"));
167
168  // --- JSON / JSONB (more) ---------------------------------------------
169
170  f!(
171    "to_json",
172    "to_json(anyelement) -> json",
173    "Same as to_jsonb but produces json (not binary).",
174    "SELECT to_json(ARRAY[1,2,3]);",
175    pg("functions-json.html")
176  );
177
178  f!(
179    "jsonb_array_elements",
180    "jsonb_array_elements(jsonb) -> setof jsonb",
181    "Expand a jsonb array to a set of jsonb values.",
182    "SELECT * FROM jsonb_array_elements('[1,2,3]'::jsonb);",
183    pg("functions-json.html")
184  );
185
186  // --- Range -----------------------------------------------------------
187
188  f!(
189    "int4multirange",
190    "int4multirange(VARIADIC int4range) -> int4multirange",
191    "Constructor for int4multirange from explicit int4range pieces (PG 14+).",
192    "SELECT int4multirange(int4range(1,4), int4range(10,12));",
193    pg("rangetypes.html#RANGETYPES-BUILTIN")
194  );
195
196  // String helpers commonly used in dynamic SQL.
197
198  f!(
199    "strpos",
200    "strpos(haystack text, needle text) -> integer",
201    "1-based index of needle in haystack, 0 if not found.",
202    "SELECT strpos('hello world','world');",
203    pg("functions-string.html")
204  );
205
206  // pg_catalog admin helpers.
207
208  f!(
209    "trim_array",
210    "trim_array(anyarray, n int) -> anyarray",
211    "Return all but the last n elements.",
212    "SELECT trim_array(ARRAY[1,2,3,4], 1);",
213    pg("functions-array.html")
214  );
215  // current_* time/date are SQL-standard functions; they also work without parens.
216  f!(
217    "current_time",
218    "current_time [(p int)] -> time with time zone",
219    "Current TIME WITH TIME ZONE (optional precision).",
220    "SELECT current_time(3);",
221    pg("functions-datetime.html")
222  );
223  f!(
224    "current_timestamp",
225    "current_timestamp [(p int)] -> timestamp with time zone",
226    "Current TIMESTAMPTZ (optional precision).",
227    "SELECT current_timestamp(0);",
228    pg("functions-datetime.html")
229  );
230  f!(
231    "current_date",
232    "current_date -> date",
233    "Current DATE (no parens).",
234    "SELECT current_date;",
235    pg("functions-datetime.html")
236  );
237
238  f!("md5", "md5(text|bytea) -> text", "MD5 hash hex digest.", "SELECT md5('hello');", pg("functions-string.html"));
239
240  f!("cbrt", "cbrt(double) -> double precision", "Cube root.", "SELECT cbrt(27);", pg("functions-math.html"));
241  f!("gcd", "gcd(int, int) -> integer", "Greatest common divisor.", "SELECT gcd(12, 18);", pg("functions-math.html"));
242  f!("lcm", "lcm(int, int) -> integer", "Least common multiple.", "SELECT lcm(4, 6);", pg("functions-math.html"));
243
244  // Statistical / regression aggregates.
245
246  f!(
247    "array_to_json",
248    "array_to_json(anyarray [, pretty bool]) -> json",
249    "Convert an array to a JSON array.",
250    "SELECT array_to_json(ARRAY[1,2,3]);",
251    pg("functions-json.html")
252  );
253
254  // Text search internals.
255
256  f!(
257    "strip",
258    "strip(tsvector) -> tsvector",
259    "Strip positions/weights from a tsvector.",
260    "SELECT strip(to_tsvector('hello world'));",
261    pg("functions-textsearch.html")
262  );
263
264  // ---- Trigonometric ----
265  f!("sin", "sin(double precision) -> double precision", "Sine, radians.", "SELECT sin(0);", pg("functions-math.html"));
266
267  // Full-text search helpers.
268  f!(
269    "setweight",
270    "setweight(tsvector, weight char) -> tsvector",
271    "Tag every lexeme with a weight letter (A/B/C/D).",
272    "SELECT setweight(to_tsvector('title'), 'A');",
273    pg("textsearch-features.html#TEXTSEARCH-MANIPULATE-TSVECTOR")
274  );
275
276  // Additional jsonb fns.
277
278  f!(
279    "jsonb_array_elements_text",
280    "jsonb_array_elements_text(jsonb) -> setof text",
281    "Expand jsonb array into rows of text.",
282    "SELECT * FROM jsonb_array_elements_text('[\"a\",\"b\"]');",
283    pg("functions-json.html")
284  );
285
286  f!(
287    "json_array_elements_text",
288    "json_array_elements_text(json) -> setof text",
289    "Expand json array as text rows.",
290    "SELECT * FROM json_array_elements_text('[\"a\"]');",
291    pg("functions-json.html")
292  );
293
294  f!(
295    "jsonb_object",
296    "jsonb_object(text[] [, text[]]) -> jsonb",
297    "Build a jsonb object from a key array (+ optional value array).",
298    "SELECT jsonb_object('{a,b}', '{1,2}');",
299    pg("functions-json.html")
300  );
301
302  // uuid-ossp extension fns (heavily used in real schemas).
303
304  f!(
305    "uuid_generate_v3",
306    "uuid_generate_v3(namespace uuid, name text) -> uuid",
307    "Name-based UUID (v3) using MD5.",
308    "SELECT uuid_generate_v3(uuid_ns_dns(), 'example.com');",
309    pg("uuid-ossp.html")
310  );
311
312  f!("uuid_nil", "uuid_nil() -> uuid", "All-zero UUID.", "SELECT uuid_nil();", pg("uuid-ossp.html"));
313  f!("uuid_ns_dns", "uuid_ns_dns() -> uuid", "DNS namespace UUID.", "SELECT uuid_ns_dns();", pg("uuid-ossp.html"));
314  f!("uuid_ns_url", "uuid_ns_url() -> uuid", "URL namespace UUID.", "SELECT uuid_ns_url();", pg("uuid-ossp.html"));
315
316  // Common math + date helpers that were missing.
317  f!("ceil", "ceil(numeric) -> numeric", "Round up to integer.", "SELECT ceil(3.2);", pg("functions-math.html"));
318
319  // ---- Missing-essentials batch ---------------------------------
320  f!(
321    "cast",
322    "CAST(expr AS type) -- explicit type conversion. Operator alias: `expr::type`.",
323    "Convert between types.",
324    "SELECT CAST('42' AS int);",
325    pg("sql-expressions.html#SQL-SYNTAX-TYPE-CASTS")
326  );
327  f!("user", "user -> name", "Current SQL role (alias for current_user).", "SELECT user;", pg("functions-info.html"));
328  f!(
329    "pg_version",
330    "version() -> text",
331    "Server version string (PG canonical name is `version`).",
332    "SELECT version();",
333    pg("functions-info.html")
334  );
335  f!(
336    "localtimestamp",
337    "localtimestamp -> timestamp",
338    "Current timestamp WITHOUT time zone, fixed for the transaction.",
339    "SELECT localtimestamp;",
340    pg("functions-datetime.html")
341  );
342  f!(
343    "localtime",
344    "localtime -> time",
345    "Current time of day WITHOUT time zone, fixed for the transaction.",
346    "SELECT localtime;",
347    pg("functions-datetime.html")
348  );
349  f!(
350    "jsonb_extract_path",
351    "jsonb_extract_path(jsonb, VARIADIC text) -> jsonb",
352    "Extract sub-object at the given key path (alias for `#>`).",
353    "SELECT jsonb_extract_path(data, 'addr','city') FROM t;",
354    pg("functions-json.html")
355  );
356  f!(
357    "jsonb_extract_path_text",
358    "jsonb_extract_path_text(jsonb, VARIADIC text) -> text",
359    "Same as jsonb_extract_path, but coerce final value to text (alias for `#>>`).",
360    "SELECT jsonb_extract_path_text(data,'addr','city');",
361    pg("functions-json.html")
362  );
363  f!(
364    "json_array_elements",
365    "json_array_elements(json) -> setof json",
366    "Expand a JSON array into a set of JSON elements.",
367    "SELECT * FROM json_array_elements('[1,2,3]'::json);",
368    pg("functions-json.html")
369  );
370  f!(
371    "pow",
372    "pow(a numeric, b numeric) -> numeric",
373    "Alias for `power(a, b)`.",
374    "SELECT pow(2, 10);",
375    pg("functions-math.html")
376  );
377  f!(
378    "log10",
379    "log10(numeric) -> numeric",
380    "Base-10 logarithm. PG canonical: `log(x)` (which is base-10).",
381    "SELECT log10(1000);",
382    pg("functions-math.html")
383  );
384
385  // ---- function sweep r93 -----------------------------------
386  f!(
387    "abbrev",
388    "abbrev(...) -> ...",
389    "Network address function (inet / cidr).",
390    "SELECT abbrev(...);",
391    pg("functions-net.html")
392  );
393  f!(
394    "acosd",
395    "acosd(numeric) -> double precision",
396    "Trigonometric function operating in degrees.",
397    "SELECT acosd(...);",
398    pg("functions-math.html")
399  );
400  f!(
401    "acosh",
402    "acosh(double precision) -> double precision",
403    "Hyperbolic / inverse-hyperbolic trig.",
404    "SELECT acosh(...);",
405    pg("functions-math.html")
406  );
407  f!(
408    "asind",
409    "asind(numeric) -> double precision",
410    "Trigonometric function operating in degrees.",
411    "SELECT asind(...);",
412    pg("functions-math.html")
413  );
414  f!(
415    "asinh",
416    "asinh(double precision) -> double precision",
417    "Hyperbolic / inverse-hyperbolic trig.",
418    "SELECT asinh(...);",
419    pg("functions-math.html")
420  );
421  f!(
422    "atan2d",
423    "atan2d(numeric) -> double precision",
424    "Trigonometric function operating in degrees.",
425    "SELECT atan2d(...);",
426    pg("functions-math.html")
427  );
428  f!(
429    "atand",
430    "atand(numeric) -> double precision",
431    "Trigonometric function operating in degrees.",
432    "SELECT atand(...);",
433    pg("functions-math.html")
434  );
435  f!(
436    "atanh",
437    "atanh(double precision) -> double precision",
438    "Hyperbolic / inverse-hyperbolic trig.",
439    "SELECT atanh(...);",
440    pg("functions-math.html")
441  );
442  f!(
443    "broadcast",
444    "broadcast(...) -> ...",
445    "Network address function (inet / cidr).",
446    "SELECT broadcast(...);",
447    pg("functions-net.html")
448  );
449  f!(
450    "cosd",
451    "cosd(numeric) -> double precision",
452    "Trigonometric function operating in degrees.",
453    "SELECT cosd(...);",
454    pg("functions-math.html")
455  );
456  f!(
457    "cosh",
458    "cosh(double precision) -> double precision",
459    "Hyperbolic / inverse-hyperbolic trig.",
460    "SELECT cosh(...);",
461    pg("functions-math.html")
462  );
463  f!(
464    "factorial",
465    "factorial(bigint) -> numeric",
466    "Factorial. Deprecated -- use `numeric_fac()` or `n!` operator.",
467    "SELECT factorial(10);",
468    pg("functions-math.html")
469  );
470  f!(
471    "family",
472    "family(...) -> ...",
473    "Network address function (inet / cidr).",
474    "SELECT family(...);",
475    pg("functions-net.html")
476  );
477  f!(
478    "hostmask",
479    "hostmask(...) -> ...",
480    "Network address function (inet / cidr).",
481    "SELECT hostmask(...);",
482    pg("functions-net.html")
483  );
484  f!(
485    "inet_client_addr",
486    "inet_client_addr(...) -> ...",
487    "Network address function (inet / cidr).",
488    "SELECT inet_client_addr(...);",
489    pg("functions-net.html")
490  );
491  f!(
492    "inet_merge",
493    "inet_merge(...) -> ...",
494    "Network address function (inet / cidr).",
495    "SELECT inet_merge(...);",
496    pg("functions-net.html")
497  );
498  f!(
499    "inet_same_family",
500    "inet_same_family(...) -> ...",
501    "Network address function (inet / cidr).",
502    "SELECT inet_same_family(...);",
503    pg("functions-net.html")
504  );
505  f!(
506    "inet_server_addr",
507    "inet_server_addr(...) -> ...",
508    "Network address function (inet / cidr).",
509    "SELECT inet_server_addr(...);",
510    pg("functions-net.html")
511  );
512  f!(
513    "masklen",
514    "masklen(...) -> ...",
515    "Network address function (inet / cidr).",
516    "SELECT masklen(...);",
517    pg("functions-net.html")
518  );
519  f!(
520    "overlaps",
521    "(t1, t2) OVERLAPS (t3, t4) -> boolean",
522    "(start1, end1) OVERLAPS (start2, end2) -- range overlap predicate.",
523    "SELECT (DATE 2024-01-01, DATE 2024-12-31) OVERLAPS (DATE 2024-06-01, DATE 2025-06-01);",
524    pg("functions-datetime.html")
525  );
526  f!(
527    "pgp_pub_decrypt",
528    "pgp_pub_decrypt(...) -> ...",
529    "pgcrypto PGP cipher function -- requires pgcrypto extension.",
530    "SELECT pgp_pub_decrypt(...);",
531    pg("pgcrypto.html")
532  );
533  f!(
534    "pgp_pub_encrypt",
535    "pgp_pub_encrypt(...) -> ...",
536    "pgcrypto PGP cipher function -- requires pgcrypto extension.",
537    "SELECT pgp_pub_encrypt(...);",
538    pg("pgcrypto.html")
539  );
540  f!(
541    "pgp_sym_decrypt",
542    "pgp_sym_decrypt(...) -> ...",
543    "pgcrypto PGP cipher function -- requires pgcrypto extension.",
544    "SELECT pgp_sym_decrypt(...);",
545    pg("pgcrypto.html")
546  );
547  f!(
548    "pgp_sym_encrypt",
549    "pgp_sym_encrypt(...) -> ...",
550    "pgcrypto PGP cipher function -- requires pgcrypto extension.",
551    "SELECT pgp_sym_encrypt(...);",
552    pg("pgcrypto.html")
553  );
554  f!(
555    "sind",
556    "sind(numeric) -> double precision",
557    "Trigonometric function operating in degrees.",
558    "SELECT sind(...);",
559    pg("functions-math.html")
560  );
561  f!(
562    "sinh",
563    "sinh(double precision) -> double precision",
564    "Hyperbolic / inverse-hyperbolic trig.",
565    "SELECT sinh(...);",
566    pg("functions-math.html")
567  );
568  f!(
569    "tand",
570    "tand(numeric) -> double precision",
571    "Trigonometric function operating in degrees.",
572    "SELECT tand(...);",
573    pg("functions-math.html")
574  );
575  f!(
576    "tanh",
577    "tanh(double precision) -> double precision",
578    "Hyperbolic / inverse-hyperbolic trig.",
579    "SELECT tanh(...);",
580    pg("functions-math.html")
581  );
582  f!(
583    "text",
584    "text(inet) -> text",
585    "Convert internet address to text representation.",
586    "SELECT text(...);",
587    pg("functions-formatting.html")
588  );
589  f!(
590    "to_ascii",
591    "to_ascii(text [, encoding]) -> text",
592    "Transliterate to 7-bit ASCII.",
593    "SELECT to_ascii(Karel);",
594    pg("functions-string.html")
595  );
596  f!(
597    "xmlagg",
598    "xmlagg(xml) -> xml",
599    "Aggregate: concatenate per-row XML values into one XML forest (use `ORDER BY` for deterministic order).",
600    "SELECT xmlagg(XMLELEMENT(NAME item, name) ORDER BY name) FROM products;",
601    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
602  );
603
604  // ---- round 148 missing fns ----
605  f!(
606    "range_intersect",
607    "range_intersect(anyrange, anyrange) -> anyrange",
608    "Intersection of two ranges.",
609    "SELECT int4range(1,10) * int4range(5,15);",
610    pg("rangetypes.html")
611  );
612  f!(
613    "range_union",
614    "range_union(anyrange, anyrange) -> anyrange",
615    "Union of two ranges (must overlap or abut).",
616    "SELECT int4range(1,5) + int4range(4,10);",
617    pg("rangetypes.html")
618  );
619  f!(
620    "range_minus",
621    "range_minus(anyrange, anyrange) -> anyrange",
622    "Difference of two ranges.",
623    "SELECT int4range(1,10) - int4range(5,8);",
624    pg("rangetypes.html")
625  );
626  f!(
627    "lower_inf",
628    "lower_inf(anyrange) -> boolean",
629    "True when the range has no lower bound.",
630    "SELECT lower_inf('(,5)'::int4range);",
631    pg("rangetypes.html")
632  );
633  f!(
634    "upper_inf",
635    "upper_inf(anyrange) -> boolean",
636    "True when the range has no upper bound.",
637    "SELECT upper_inf('[1,)'::int4range);",
638    pg("rangetypes.html")
639  );
640  f!(
641    "pg_notify",
642    "pg_notify(channel text, payload text) -> void",
643    "Send a NOTIFY without requiring a literal channel name.",
644    "SELECT pg_notify('chan', 'payload');",
645    pg("functions-admin.html")
646  );
647  f!(
648    "pg_listening_channels",
649    "pg_listening_channels() -> setof text",
650    "Names of all channels the session is listening on.",
651    "SELECT pg_listening_channels();",
652    pg("functions-info.html")
653  );
654  f!(
655    "pg_current_xact_id_if_assigned",
656    "pg_current_xact_id_if_assigned() -> xid8",
657    "Current xid or NULL (does not assign one).",
658    "SELECT pg_current_xact_id_if_assigned();",
659    pg("functions-info.html")
660  );
661  f!(
662    "txid_current",
663    "txid_current() -> bigint",
664    "Deprecated -- use pg_current_xact_id() instead.",
665    "SELECT txid_current();",
666    pg("functions-info.html")
667  );
668  f!(
669    "pg_export_snapshot",
670    "pg_export_snapshot() -> text",
671    "Export the current snapshot id for parallel transactions.",
672    "SELECT pg_export_snapshot();",
673    pg("functions-admin.html")
674  );
675  f!(
676    "pg_tablespace_size",
677    "pg_tablespace_size(oid|text) -> bigint",
678    "Disk space used by a tablespace.",
679    "SELECT pg_size_pretty(pg_tablespace_size('pg_default'));",
680    pg("functions-admin.html")
681  );
682  f!(
683    "pg_size_bytes",
684    "pg_size_bytes(text) -> bigint",
685    "Parse a human-readable size string back into bytes.",
686    "SELECT pg_size_bytes('64 MB');",
687    pg("functions-admin.html")
688  );
689  f!(
690    "pg_column_size",
691    "pg_column_size(any) -> int",
692    "Bytes used to store a particular value (incl. TOAST overhead).",
693    "SELECT pg_column_size(payload) FROM t;",
694    pg("functions-admin.html")
695  );
696  f!(
697    "pg_relation_filenode",
698    "pg_relation_filenode(regclass) -> oid",
699    "On-disk filenode for a relation.",
700    "SELECT pg_relation_filenode('users');",
701    pg("functions-admin.html")
702  );
703
704  // ---- round 149 missing fns ----
705  f!(
706    "pg_blocking_pids",
707    "pg_blocking_pids(pid int) -> int[]",
708    "PIDs of sessions whose locks block the given PID.",
709    "SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity;",
710    pg("functions-info.html")
711  );
712  f!(
713    "pg_backend_pid",
714    "pg_backend_pid() -> int",
715    "PID of the server process attached to the current session.",
716    "SELECT pg_backend_pid();",
717    pg("functions-info.html")
718  );
719  f!(
720    "pg_my_temp_schema",
721    "pg_my_temp_schema() -> oid",
722    "OID of the session's TEMP schema, or 0 if none.",
723    "SELECT pg_my_temp_schema();",
724    pg("functions-info.html")
725  );
726  f!(
727    "pg_xact_commit_timestamp",
728    "pg_xact_commit_timestamp(xid) -> timestamptz",
729    "Commit time of a transaction (requires track_commit_timestamp).",
730    "SELECT pg_xact_commit_timestamp(xmin) FROM t;",
731    pg("functions-info.html")
732  );
733  f!(
734    "pg_last_committed_xact",
735    "pg_last_committed_xact() -> (xid, timestamptz, repl_origin_oid)",
736    "Most recently committed transaction.",
737    "SELECT * FROM pg_last_committed_xact();",
738    pg("functions-info.html")
739  );
740  f!(
741    "pg_last_xact_replay_timestamp",
742    "pg_last_xact_replay_timestamp() -> timestamptz",
743    "Standby: when the last replayed transaction committed on primary.",
744    "SELECT pg_last_xact_replay_timestamp();",
745    pg("functions-admin.html")
746  );
747  f!(
748    "pg_is_in_recovery",
749    "pg_is_in_recovery() -> boolean",
750    "True when the server is in recovery / standby mode.",
751    "SELECT pg_is_in_recovery();",
752    pg("functions-admin.html")
753  );
754  f!(
755    "pg_promote",
756    "pg_promote(wait boolean, wait_seconds int) -> boolean",
757    "Promote a standby to primary.",
758    "SELECT pg_promote();",
759    pg("functions-admin.html")
760  );
761  f!(
762    "pg_postmaster_start_time",
763    "pg_postmaster_start_time() -> timestamptz",
764    "When the server started.",
765    "SELECT pg_postmaster_start_time();",
766    pg("functions-info.html")
767  );
768  f!(
769    "pg_conf_load_time",
770    "pg_conf_load_time() -> timestamptz",
771    "When the server last reloaded postgresql.conf.",
772    "SELECT pg_conf_load_time();",
773    pg("functions-info.html")
774  );
775  f!(
776    "pg_reload_conf",
777    "pg_reload_conf() -> boolean",
778    "Send SIGHUP so the server reloads its config.",
779    "SELECT pg_reload_conf();",
780    pg("functions-admin.html")
781  );
782  f!(
783    "pg_rotate_logfile",
784    "pg_rotate_logfile() -> boolean",
785    "Rotate the server's log file.",
786    "SELECT pg_rotate_logfile();",
787    pg("functions-admin.html")
788  );
789  f!(
790    "pg_get_keywords",
791    "pg_get_keywords() -> setof (word, catcode, catdesc)",
792    "All known PG keywords + their category.",
793    "SELECT * FROM pg_get_keywords();",
794    pg("functions-info.html")
795  );
796
797  // ---- round 150 replication + WAL ----
798  f!(
799    "pg_create_logical_replication_slot",
800    "pg_create_logical_replication_slot(slot_name name, plugin name [, temporary boolean] [, twophase boolean]) -> (slot_name, lsn)",
801    "Create a logical replication slot bound to a plugin.",
802    "SELECT pg_create_logical_replication_slot('s1', 'pgoutput');",
803    pg("functions-admin.html")
804  );
805  f!(
806    "pg_create_physical_replication_slot",
807    "pg_create_physical_replication_slot(slot_name name [, immediately_reserve boolean] [, temporary boolean]) -> (slot_name, lsn)",
808    "Create a physical replication slot.",
809    "SELECT pg_create_physical_replication_slot('standby1');",
810    pg("functions-admin.html")
811  );
812  f!(
813    "pg_drop_replication_slot",
814    "pg_drop_replication_slot(slot_name name) -> void",
815    "Drop a replication slot.",
816    "SELECT pg_drop_replication_slot('s1');",
817    pg("functions-admin.html")
818  );
819  f!(
820    "pg_replication_slot_advance",
821    "pg_replication_slot_advance(slot_name name, upto_lsn pg_lsn) -> (slot_name, end_lsn)",
822    "Advance a slot's confirmed_flush_lsn.",
823    "SELECT pg_replication_slot_advance('s1', '0/0');",
824    pg("functions-admin.html")
825  );
826  f!(
827    "pg_logical_slot_get_changes",
828    "pg_logical_slot_get_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges int, VARIADIC options text[]) -> setof (lsn, xid, data)",
829    "Stream decoded changes from a slot.",
830    "SELECT * FROM pg_logical_slot_get_changes('s1', NULL, NULL);",
831    pg("functions-admin.html")
832  );
833  f!(
834    "pg_logical_emit_message",
835    "pg_logical_emit_message(transactional boolean, prefix text, content text) -> pg_lsn",
836    "Inject a custom message into the WAL stream.",
837    "SELECT pg_logical_emit_message(true, 'app', 'payload');",
838    pg("functions-admin.html")
839  );
840  f!(
841    "pg_wal_lsn_diff",
842    "pg_wal_lsn_diff(lsn1 pg_lsn, lsn2 pg_lsn) -> numeric",
843    "Bytes between two WAL positions.",
844    "SELECT pg_wal_lsn_diff('0/2', '0/0');",
845    pg("functions-admin.html")
846  );
847  f!(
848    "pg_current_wal_lsn",
849    "pg_current_wal_lsn() -> pg_lsn",
850    "Current WAL write position (primary only).",
851    "SELECT pg_current_wal_lsn();",
852    pg("functions-admin.html")
853  );
854  f!(
855    "pg_current_wal_insert_lsn",
856    "pg_current_wal_insert_lsn() -> pg_lsn",
857    "Current WAL insertion position.",
858    "SELECT pg_current_wal_insert_lsn();",
859    pg("functions-admin.html")
860  );
861  f!(
862    "pg_current_wal_flush_lsn",
863    "pg_current_wal_flush_lsn() -> pg_lsn",
864    "Current WAL flush position.",
865    "SELECT pg_current_wal_flush_lsn();",
866    pg("functions-admin.html")
867  );
868  f!(
869    "pg_last_wal_receive_lsn",
870    "pg_last_wal_receive_lsn() -> pg_lsn",
871    "Standby: last WAL position received.",
872    "SELECT pg_last_wal_receive_lsn();",
873    pg("functions-admin.html")
874  );
875  f!(
876    "pg_last_wal_replay_lsn",
877    "pg_last_wal_replay_lsn() -> pg_lsn",
878    "Standby: last WAL position replayed.",
879    "SELECT pg_last_wal_replay_lsn();",
880    pg("functions-admin.html")
881  );
882  f!(
883    "pg_switch_wal",
884    "pg_switch_wal() -> pg_lsn",
885    "Force a WAL switch -- start a new segment.",
886    "SELECT pg_switch_wal();",
887    pg("functions-admin.html")
888  );
889  f!(
890    "pg_backup_start",
891    "pg_backup_start(label text, fast boolean) -> pg_lsn",
892    "PG15+: start a non-exclusive base backup.",
893    "SELECT pg_backup_start('weekly', false);",
894    pg("functions-admin.html")
895  );
896  f!(
897    "pg_backup_stop",
898    "pg_backup_stop(wait_for_archive boolean) -> (lsn, labelfile, spcmapfile)",
899    "PG15+: stop a base backup, return backup info.",
900    "SELECT * FROM pg_backup_stop(true);",
901    pg("functions-admin.html")
902  );
903  f!(
904    "pg_walfile_name",
905    "pg_walfile_name(lsn pg_lsn) -> text",
906    "WAL segment file name containing this LSN.",
907    "SELECT pg_walfile_name(pg_current_wal_lsn());",
908    pg("functions-admin.html")
909  );
910  f!(
911    "pg_walfile_name_offset",
912    "pg_walfile_name_offset(lsn pg_lsn) -> (file_name, file_offset)",
913    "WAL segment file name + byte offset for an LSN.",
914    "SELECT * FROM pg_walfile_name_offset(pg_current_wal_lsn());",
915    pg("functions-admin.html")
916  );
917
918  // ---- round 151 misc gaps ----
919  f!(
920    "string_to_table",
921    "string_to_table(text, delim text [, null_string text]) -> setof text",
922    "Like string_to_array but returns a row set.",
923    "SELECT * FROM string_to_table('a,b,c', ',');",
924    pg("functions-string.html")
925  );
926  f!(
927    "jsonb_set_lax",
928    "jsonb_set_lax(jsonb, path text[], new_value jsonb [, create_missing boolean] [, null_value_treatment text]) -> jsonb",
929    "Like jsonb_set but adds 'null_value_treatment' option (PG13+).",
930    "SELECT jsonb_set_lax(data, '{x}', 'null'::jsonb, true, 'delete_key');",
931    pg("functions-json.html")
932  );
933
934  // ---- round 152 missing system/privilege fns ----
935  f!(
936    "current_role",
937    "current_role -> name",
938    "Same as current_user.",
939    "SELECT current_role;",
940    pg("functions-info.html")
941  );
942  f!(
943    "current_query",
944    "current_query() -> text",
945    "Text of the currently executing query.",
946    "SELECT current_query();",
947    pg("functions-info.html")
948  );
949  f!(
950    "current_catalog",
951    "current_catalog -> name",
952    "SQL standard alias for current_database.",
953    "SELECT current_catalog;",
954    pg("functions-info.html")
955  );
956  f!(
957    "pg_current_logfile",
958    "pg_current_logfile([format text]) -> text",
959    "Path of the current server log file.",
960    "SELECT pg_current_logfile();",
961    pg("functions-info.html")
962  );
963  f!(
964    "inet_client_port",
965    "inet_client_port() -> int",
966    "Client port; NULL when over Unix socket.",
967    "SELECT inet_client_port();",
968    pg("functions-info.html")
969  );
970  f!(
971    "inet_server_port",
972    "inet_server_port() -> int",
973    "Server port the connection landed on.",
974    "SELECT inet_server_port();",
975    pg("functions-info.html")
976  );
977  f!(
978    "has_table_privilege",
979    "has_table_privilege([user,] table, privilege text) -> boolean",
980    "Does the role have the privilege on the table?",
981    "SELECT has_table_privilege('alice', 'users', 'SELECT');",
982    pg("functions-info.html")
983  );
984  f!(
985    "has_schema_privilege",
986    "has_schema_privilege([user,] schema, privilege text) -> boolean",
987    "Schema-level privilege check.",
988    "SELECT has_schema_privilege('public', 'USAGE');",
989    pg("functions-info.html")
990  );
991  f!(
992    "has_database_privilege",
993    "has_database_privilege([user,] database, privilege text) -> boolean",
994    "Database-level privilege check.",
995    "SELECT has_database_privilege(current_user, current_database(), 'CONNECT');",
996    pg("functions-info.html")
997  );
998  f!(
999    "has_column_privilege",
1000    "has_column_privilege([user,] table, column, privilege text) -> boolean",
1001    "Column-level privilege check.",
1002    "SELECT has_column_privilege('alice', 'users', 'email', 'UPDATE');",
1003    pg("functions-info.html")
1004  );
1005  f!(
1006    "has_function_privilege",
1007    "has_function_privilege([user,] function, privilege text) -> boolean",
1008    "Function execution privilege.",
1009    "SELECT has_function_privilege('alice', 'now()', 'EXECUTE');",
1010    pg("functions-info.html")
1011  );
1012  f!(
1013    "has_any_column_privilege",
1014    "has_any_column_privilege([user,] table, privilege text) -> boolean",
1015    "Does the role have privilege on ANY column?",
1016    "SELECT has_any_column_privilege('alice', 'users', 'SELECT');",
1017    pg("functions-info.html")
1018  );
1019  f!(
1020    "has_sequence_privilege",
1021    "has_sequence_privilege([user,] sequence, privilege text) -> boolean",
1022    "Sequence privilege check.",
1023    "SELECT has_sequence_privilege('s', 'USAGE');",
1024    pg("functions-info.html")
1025  );
1026  f!(
1027    "has_tablespace_privilege",
1028    "has_tablespace_privilege([user,] tablespace, privilege text) -> boolean",
1029    "Tablespace privilege check.",
1030    "SELECT has_tablespace_privilege('pg_default', 'CREATE');",
1031    pg("functions-info.html")
1032  );
1033  f!(
1034    "has_foreign_data_wrapper_privilege",
1035    "has_foreign_data_wrapper_privilege([user,] fdw, privilege text) -> boolean",
1036    "FDW privilege check.",
1037    "SELECT has_foreign_data_wrapper_privilege('postgres_fdw', 'USAGE');",
1038    pg("functions-info.html")
1039  );
1040  f!(
1041    "has_language_privilege",
1042    "has_language_privilege([user,] lang, privilege text) -> boolean",
1043    "Procedural language privilege.",
1044    "SELECT has_language_privilege('plpgsql', 'USAGE');",
1045    pg("functions-info.html")
1046  );
1047  f!(
1048    "has_server_privilege",
1049    "has_server_privilege([user,] server, privilege text) -> boolean",
1050    "Foreign server privilege check.",
1051    "SELECT has_server_privilege('srv1', 'USAGE');",
1052    pg("functions-info.html")
1053  );
1054  f!(
1055    "has_type_privilege",
1056    "has_type_privilege([user,] type, privilege text) -> boolean",
1057    "Type privilege check.",
1058    "SELECT has_type_privilege('point', 'USAGE');",
1059    pg("functions-info.html")
1060  );
1061  f!(
1062    "pg_has_role",
1063    "pg_has_role([user,] role, privilege text) -> boolean",
1064    "Is `user` a member of `role` with the requested membership?",
1065    "SELECT pg_has_role('alice', 'admins', 'MEMBER');",
1066    pg("functions-info.html")
1067  );
1068  f!(
1069    "row_security_active",
1070    "row_security_active(table) -> boolean",
1071    "True when RLS would be enforced on the table for the current role.",
1072    "SELECT row_security_active('users');",
1073    pg("functions-info.html")
1074  );
1075
1076  // ---- round 153 pg_catalog fns ----
1077
1078  f!(
1079    "pg_get_partkeydef",
1080    "pg_get_partkeydef(regclass) -> text",
1081    "Definition of the PARTITION BY key for a partitioned table.",
1082    "SELECT pg_get_partkeydef('orders');",
1083    pg("functions-info.html")
1084  );
1085  f!(
1086    "pg_get_publication_tables",
1087    "pg_get_publication_tables(pubname text) -> setof oid",
1088    "OIDs of tables included in a publication.",
1089    "SELECT * FROM pg_get_publication_tables('pub1');",
1090    pg("functions-info.html")
1091  );
1092
1093  f!(
1094    "pg_relation_is_publishable",
1095    "pg_relation_is_publishable(regclass) -> boolean",
1096    "True if the relation can be added to a publication.",
1097    "SELECT pg_relation_is_publishable('users');",
1098    pg("functions-info.html")
1099  );
1100  f!(
1101    "pg_get_replica_identity_index",
1102    "pg_get_replica_identity_index(regclass) -> regclass",
1103    "Index used as REPLICA IDENTITY for the table, or NULL.",
1104    "SELECT pg_get_replica_identity_index('users');",
1105    pg("functions-info.html")
1106  );
1107  f!(
1108    "pg_get_object_address",
1109    "pg_get_object_address(type text, name text[], args text[]) -> (classid, objid, objsubid)",
1110    "Canonical address record for an object.",
1111    "SELECT * FROM pg_get_object_address('table', '{public,users}', '{}');",
1112    pg("functions-info.html")
1113  );
1114  f!(
1115    "pg_identify_object",
1116    "pg_identify_object(classid oid, objid oid, objsubid int) -> (type, schema, name, identity)",
1117    "Identify an object by its catalog address.",
1118    "SELECT * FROM pg_identify_object('pg_class'::regclass, 'users'::regclass, 0);",
1119    pg("functions-info.html")
1120  );
1121  f!(
1122    "pg_identify_object_as_address",
1123    "pg_identify_object_as_address(classid oid, objid oid, objsubid int) -> (type text, object_names text[], object_args text[])",
1124    "Identify an object as a usable address.",
1125    "SELECT * FROM pg_identify_object_as_address('pg_class'::regclass, 'users'::regclass, 0);",
1126    pg("functions-info.html")
1127  );
1128  f!(
1129    "pg_describe_object",
1130    "pg_describe_object(classid oid, objid oid, objsubid int) -> text",
1131    "Human-readable description of an object.",
1132    "SELECT pg_describe_object('pg_class'::regclass, 'users'::regclass, 0);",
1133    pg("functions-info.html")
1134  );
1135  f!(
1136    "pg_locks",
1137    "pg_locks -> view",
1138    "View exposing every active lock; useful for blocking diagnostics.",
1139    "SELECT * FROM pg_locks WHERE granted = false;",
1140    pg("view-pg-locks.html")
1141  );
1142  f!(
1143    "pg_stat_get_backend_idset",
1144    "pg_stat_get_backend_idset() -> setof int",
1145    "Internal: enumerate backend ids for pg_stat_get_backend_*.",
1146    "SELECT pg_stat_get_backend_idset();",
1147    pg("monitoring-stats.html")
1148  );
1149  f!(
1150    "pg_get_ruledef",
1151    "pg_get_ruledef(rule_oid oid [, pretty boolean]) -> text",
1152    "Reconstruct a CREATE RULE statement.",
1153    "SELECT pg_get_ruledef(oid) FROM pg_rewrite;",
1154    pg("functions-info.html")
1155  );
1156  f!(
1157    "pg_get_function_identity_arguments",
1158    "pg_get_function_identity_arguments(funcid oid) -> text",
1159    "Function arg signature used in DROP/COMMENT/etc statements.",
1160    "SELECT pg_get_function_identity_arguments(oid) FROM pg_proc;",
1161    pg("functions-info.html")
1162  );
1163
1164  // ---- round 160 string fns ----
1165  f!(
1166    "regexp_instr",
1167    "regexp_instr(string text, pattern text [, position int] [, occurrence int] [, return_opt int] [, flags text]) -> int",
1168    "Position of a regexp match (PG15+).",
1169    "SELECT regexp_instr('foo123bar', '[0-9]+');",
1170    pg("functions-matching.html")
1171  );
1172  f!(
1173    "regexp_like",
1174    "regexp_like(string text, pattern text [, flags text]) -> boolean",
1175    "Boolean regexp match (PG15+).",
1176    "WHERE regexp_like(email, '^.+@.+\\..+$', 'i')",
1177    pg("functions-matching.html")
1178  );
1179  f!(
1180    "similar_to_escape",
1181    "similar_to_escape(pattern text [, escape text]) -> text",
1182    "Convert a SIMILAR TO pattern to a regular expression.",
1183    "SELECT similar_to_escape('he%lo', '#');",
1184    pg("functions-matching.html")
1185  );
1186  f!(
1187    "unistr",
1188    "unistr(text [, escape text]) -> text",
1189    "Decode Unicode escape sequences in a string (PG16+).",
1190    "SELECT unistr('\\u0041\\u0042');",
1191    pg("functions-string.html")
1192  );
1193
1194  // ---- round 161 datetime fns ----
1195  f!(
1196    "date_add",
1197    "date_add(timestamptz, interval [, timezone text]) -> timestamptz",
1198    "Add an interval to a timestamptz with zone-aware semantics (PG16+).",
1199    "SELECT date_add(now(), interval '1 month', 'UTC');",
1200    pg("functions-datetime.html")
1201  );
1202  f!(
1203    "date_subtract",
1204    "date_subtract(timestamptz, interval [, timezone text]) -> timestamptz",
1205    "Subtract an interval from a timestamptz with zone-aware semantics (PG16+).",
1206    "SELECT date_subtract(now(), interval '1 month', 'UTC');",
1207    pg("functions-datetime.html")
1208  );
1209  f!(
1210    "interval_eq",
1211    "interval_eq(a interval, b interval) -> boolean",
1212    "Internal comparison: a = b. Use the `=` operator instead in user code.",
1213    "SELECT interval_eq('1 day', '24 hours');",
1214    pg("functions-datetime.html")
1215  );
1216
1217  // ---- round 162 json record fns ----
1218  f!(
1219    "jsonb_to_record",
1220    "jsonb_to_record(jsonb) -> record",
1221    "Cast a jsonb object into a record. Used in `AS (col type, ...)` syntax.",
1222    "SELECT * FROM jsonb_to_record(payload) AS x(id int, name text);",
1223    pg("functions-json.html")
1224  );
1225  f!(
1226    "json_to_record",
1227    "json_to_record(json) -> record",
1228    "Cast a json object into a record. Used in `AS (col type, ...)` syntax.",
1229    "SELECT * FROM json_to_record(my_json) AS x(id int);",
1230    pg("functions-json.html")
1231  );
1232  f!(
1233    "jsonb_to_recordset",
1234    "jsonb_to_recordset(jsonb) -> setof record",
1235    "Cast a jsonb array of objects into a row set.",
1236    "SELECT * FROM jsonb_to_recordset(payload) AS x(id int, name text);",
1237    pg("functions-json.html")
1238  );
1239  f!(
1240    "json_to_recordset",
1241    "json_to_recordset(json) -> setof record",
1242    "Cast a json array of objects into a row set.",
1243    "SELECT * FROM json_to_recordset(my_json) AS x(id int);",
1244    pg("functions-json.html")
1245  );
1246  f!(
1247    "jsonb_populate_record",
1248    "jsonb_populate_record(base anyelement, jsonb) -> anyelement",
1249    "Cast jsonb into a row type; base is a row prototype.",
1250    "SELECT (jsonb_populate_record(NULL::users, payload)).*;",
1251    pg("functions-json.html")
1252  );
1253  f!(
1254    "json_populate_record",
1255    "json_populate_record(base anyelement, json) -> anyelement",
1256    "Cast json into a row type; base is a row prototype.",
1257    "SELECT (json_populate_record(NULL::users, my_json)).*;",
1258    pg("functions-json.html")
1259  );
1260  f!(
1261    "jsonb_populate_recordset",
1262    "jsonb_populate_recordset(base anyelement, jsonb) -> setof anyelement",
1263    "Cast jsonb array of objects into a row set of `base`.",
1264    "SELECT * FROM jsonb_populate_recordset(NULL::users, payload);",
1265    pg("functions-json.html")
1266  );
1267  f!(
1268    "jsonb_concat",
1269    "jsonb_concat(jsonb, jsonb) -> jsonb",
1270    "Internal name for the `||` operator on jsonb. Prefer the operator.",
1271    "SELECT a || b;",
1272    pg("functions-json.html")
1273  );
1274  f!(
1275    "jsonb_delete_path",
1276    "jsonb_delete_path(jsonb, path text[]) -> jsonb",
1277    "Internal name for the `#-` operator. Prefer the operator.",
1278    "SELECT data #- '{addr,city}'::text[];",
1279    pg("functions-json.html")
1280  );
1281
1282  // ---- round 163 json operator fns + xml ----
1283  f!(
1284    "json_array_element",
1285    "json_array_element(json, int) -> json",
1286    "Internal name for `json -> int`. Use the operator.",
1287    "SELECT my_json -> 0;",
1288    pg("functions-json.html")
1289  );
1290  f!(
1291    "json_array_element_text",
1292    "json_array_element_text(json, int) -> text",
1293    "Internal name for `json ->> int`. Use the operator.",
1294    "SELECT my_json ->> 0;",
1295    pg("functions-json.html")
1296  );
1297  f!(
1298    "jsonb_array_element",
1299    "jsonb_array_element(jsonb, int) -> jsonb",
1300    "Internal name for `jsonb -> int`. Use the operator.",
1301    "SELECT data -> 0;",
1302    pg("functions-json.html")
1303  );
1304  f!(
1305    "jsonb_array_element_text",
1306    "jsonb_array_element_text(jsonb, int) -> text",
1307    "Internal name for `jsonb ->> int`. Use the operator.",
1308    "SELECT data ->> 0;",
1309    pg("functions-json.html")
1310  );
1311  f!(
1312    "json_object_field",
1313    "json_object_field(json, text) -> json",
1314    "Internal name for `json -> text`. Use the operator.",
1315    "SELECT my_json -> 'addr';",
1316    pg("functions-json.html")
1317  );
1318  f!(
1319    "json_object_field_text",
1320    "json_object_field_text(json, text) -> text",
1321    "Internal name for `json ->> text`. Use the operator.",
1322    "SELECT my_json ->> 'name';",
1323    pg("functions-json.html")
1324  );
1325  f!(
1326    "jsonb_object_field",
1327    "jsonb_object_field(jsonb, text) -> jsonb",
1328    "Internal name for `jsonb -> text`. Use the operator.",
1329    "SELECT data -> 'addr';",
1330    pg("functions-json.html")
1331  );
1332  f!(
1333    "jsonb_object_field_text",
1334    "jsonb_object_field_text(jsonb, text) -> text",
1335    "Internal name for `jsonb ->> text`. Use the operator.",
1336    "SELECT data ->> 'name';",
1337    pg("functions-json.html")
1338  );
1339  f!(
1340    "query_to_xml",
1341    "query_to_xml(query text, nulls boolean, tableforest boolean, targetns text) -> xml",
1342    "Map a query result into XML.",
1343    "SELECT query_to_xml('SELECT 1 AS a', true, false, '');",
1344    pg("functions-xml.html")
1345  );
1346  f!(
1347    "schema_to_xml",
1348    "schema_to_xml(schema name, nulls boolean, tableforest boolean, targetns text) -> xml",
1349    "Map every table in a schema into XML.",
1350    "SELECT schema_to_xml('public', true, false, '');",
1351    pg("functions-xml.html")
1352  );
1353  f!(
1354    "table_to_xml",
1355    "table_to_xml(tbl regclass, nulls boolean, tableforest boolean, targetns text) -> xml",
1356    "Map a table into XML.",
1357    "SELECT table_to_xml('users', true, false, '');",
1358    pg("functions-xml.html")
1359  );
1360  f!(
1361    "cursor_to_xml",
1362    "cursor_to_xml(cursor refcursor, count int, nulls boolean, tableforest boolean, targetns text) -> xml",
1363    "Drain a cursor into XML.",
1364    "SELECT cursor_to_xml('c1', 100, true, false, '');",
1365    pg("functions-xml.html")
1366  );
1367  f!(
1368    "database_to_xml",
1369    "database_to_xml(nulls boolean, tableforest boolean, targetns text) -> xml",
1370    "Whole-database XML dump.",
1371    "SELECT database_to_xml(true, false, '');",
1372    pg("functions-xml.html")
1373  );
1374  f!(
1375    "xpath",
1376    "xpath(xpath text, xml xml [, nsarray text[]]) -> xml[]",
1377    "Apply an XPath expression to an XML document.",
1378    "SELECT xpath('//book/title', payload);",
1379    pg("functions-xml.html")
1380  );
1381  f!(
1382    "xpath_exists",
1383    "xpath_exists(xpath text, xml xml [, nsarray text[]]) -> boolean",
1384    "True when XPath matches anything in the document.",
1385    "WHERE xpath_exists('//flag', payload)",
1386    pg("functions-xml.html")
1387  );
1388  f!(
1389    "xmltable",
1390    "XMLTABLE(<xpath> PASSING <doc> COLUMNS (...) ) -> setof record",
1391    "Project XML into rows (SQL/XML).",
1392    "SELECT * FROM XMLTABLE('/r/it' PASSING xmldoc COLUMNS id int PATH '@id');",
1393    pg("functions-xml.html")
1394  );
1395  f!(
1396    "xmlcomment",
1397    "xmlcomment(text) -> xml",
1398    "Wrap text in <!-- ... -->.",
1399    "SELECT xmlcomment('section');",
1400    pg("functions-xml.html")
1401  );
1402  f!(
1403    "xmlconcat",
1404    "xmlconcat(xml[]) -> xml",
1405    "Concatenate XML fragments.",
1406    "SELECT xmlconcat(a, b);",
1407    pg("functions-xml.html")
1408  );
1409  f!(
1410    "xmlelement",
1411    "XMLELEMENT(NAME name [, XMLATTRIBUTES (...)], <content>) -> xml",
1412    "Build an XML element from SQL.",
1413    "SELECT xmlelement(name 'r', xmlattributes('1' as id), 'body');",
1414    pg("functions-xml.html")
1415  );
1416  f!(
1417    "xmlexists",
1418    "xmlexists(xpath text PASSING xml [, ns]) -> boolean",
1419    "XPath existence test.",
1420    "WHERE xmlexists('/x' PASSING xmldoc)",
1421    pg("functions-xml.html")
1422  );
1423  f!(
1424    "xmlforest",
1425    "xmlforest(<expr> AS <name>[, ...]) -> xml",
1426    "Build a sequence of XML elements.",
1427    "SELECT xmlforest(id AS pk, name);",
1428    pg("functions-xml.html")
1429  );
1430  f!(
1431    "xmlroot",
1432    "xmlroot(xml, VERSION ver text [, STANDALONE yn]) -> xml",
1433    "Replace or set the XML root declaration.",
1434    "SELECT xmlroot(payload, VERSION '1.0', STANDALONE YES);",
1435    pg("functions-xml.html")
1436  );
1437  f!(
1438    "xmlserialize",
1439    "XMLSERIALIZE({DOCUMENT|CONTENT} <xml> AS <text_type>) -> text",
1440    "Serialise XML into TEXT/VARCHAR/CHARACTER.",
1441    "SELECT xmlserialize(document payload as text);",
1442    pg("functions-xml.html")
1443  );
1444  f!(
1445    "xmlpi",
1446    "xmlpi(NAME name [, content text]) -> xml",
1447    "Build a processing instruction.",
1448    "SELECT xmlpi(name 'xml-stylesheet', 'href=...');",
1449    pg("functions-xml.html")
1450  );
1451  f!(
1452    "xmltext",
1453    "xmltext(text) -> xml",
1454    "Wrap a text value as XML text node.",
1455    "SELECT xmltext('hello');",
1456    pg("functions-xml.html")
1457  );
1458
1459  // ---- round 164 range comparison + fts fns ----
1460  f!(
1461    "range_contains",
1462    "range_contains(anyrange, anyrange) -> boolean",
1463    "Internal name for the `@>` operator on ranges.",
1464    "SELECT '[1,10)'::int4range @> '[3,5)'::int4range;",
1465    pg("rangetypes.html")
1466  );
1467  f!(
1468    "range_overlaps",
1469    "range_overlaps(anyrange, anyrange) -> boolean",
1470    "Internal name for the `&&` operator on ranges.",
1471    "SELECT '[1,10)'::int4range && '[5,15)'::int4range;",
1472    pg("rangetypes.html")
1473  );
1474  f!(
1475    "range_eq",
1476    "range_eq(anyrange, anyrange) -> boolean",
1477    "Internal name for `=` on ranges.",
1478    "SELECT a = b FROM t;",
1479    pg("rangetypes.html")
1480  );
1481  f!(
1482    "range_lt",
1483    "range_lt(anyrange, anyrange) -> boolean",
1484    "Internal name for `<` on ranges.",
1485    "SELECT a < b FROM t;",
1486    pg("rangetypes.html")
1487  );
1488  f!(
1489    "range_le",
1490    "range_le(anyrange, anyrange) -> boolean",
1491    "Internal name for `<=` on ranges.",
1492    "SELECT a <= b FROM t;",
1493    pg("rangetypes.html")
1494  );
1495  f!(
1496    "range_gt",
1497    "range_gt(anyrange, anyrange) -> boolean",
1498    "Internal name for `>` on ranges.",
1499    "SELECT a > b FROM t;",
1500    pg("rangetypes.html")
1501  );
1502  f!(
1503    "range_ge",
1504    "range_ge(anyrange, anyrange) -> boolean",
1505    "Internal name for `>=` on ranges.",
1506    "SELECT a >= b FROM t;",
1507    pg("rangetypes.html")
1508  );
1509  f!(
1510    "range_after",
1511    "range_after(anyrange, anyrange) -> boolean",
1512    "Internal name for the `>>` operator -- strictly after.",
1513    "SELECT a >> b FROM t;",
1514    pg("rangetypes.html")
1515  );
1516  f!(
1517    "range_before",
1518    "range_before(anyrange, anyrange) -> boolean",
1519    "Internal name for the `<<` operator -- strictly before.",
1520    "SELECT a << b FROM t;",
1521    pg("rangetypes.html")
1522  );
1523  f!(
1524    "range_adjacent",
1525    "range_adjacent(anyrange, anyrange) -> boolean",
1526    "Internal name for the `-|-` operator -- adjacent ranges.",
1527    "SELECT a -|- b FROM t;",
1528    pg("rangetypes.html")
1529  );
1530  f!(
1531    "unnest_multirange",
1532    "unnest(anymultirange) -> setof anyrange",
1533    "Expand a multirange into its constituent ranges. Alias of `unnest`.",
1534    "SELECT * FROM unnest('{[1,5), [10,15)}'::int4multirange);",
1535    pg("rangetypes.html")
1536  );
1537  f!(
1538    "ts_filter",
1539    "ts_filter(tsvector, char[]) -> tsvector",
1540    "Keep only lexemes with the given weights.",
1541    "SELECT ts_filter(to_tsvector('a b c'), '{A,B}');",
1542    pg("textsearch-functions.html")
1543  );
1544  f!(
1545    "ts_delete",
1546    "ts_delete(tsvector, text|text[]) -> tsvector",
1547    "Remove lexemes from a tsvector.",
1548    "SELECT ts_delete(vec, 'noise');",
1549    pg("textsearch-functions.html")
1550  );
1551  f!(
1552    "ts_rewrite",
1553    "ts_rewrite(query tsquery, target tsquery, substitute tsquery) -> tsquery",
1554    "Rewrite occurrences of `target` in `query` with `substitute`.",
1555    "SELECT ts_rewrite('cat'::tsquery, 'cat'::tsquery, 'feline'::tsquery);",
1556    pg("textsearch-functions.html")
1557  );
1558
1559  // ---- round 165 geometric fns ----
1560  f!(
1561    "area",
1562    "area(box|polygon|circle) -> double precision",
1563    "Area of a geometric object.",
1564    "SELECT area(box '(0,0),(1,1)');",
1565    pg("functions-geometry.html")
1566  );
1567  f!(
1568    "diameter",
1569    "diameter(circle) -> double precision",
1570    "Diameter of a circle.",
1571    "SELECT diameter(circle '<(0,0),5>');",
1572    pg("functions-geometry.html")
1573  );
1574  f!(
1575    "height",
1576    "height(box) -> double precision",
1577    "Vertical span of a box.",
1578    "SELECT height(box '(0,0),(3,4)');",
1579    pg("functions-geometry.html")
1580  );
1581  f!(
1582    "width",
1583    "width(box) -> double precision",
1584    "Horizontal span of a box.",
1585    "SELECT width(box '(0,0),(3,4)');",
1586    pg("functions-geometry.html")
1587  );
1588  f!(
1589    "slope",
1590    "slope(point, point) -> double precision",
1591    "Slope of the line through two points.",
1592    "SELECT slope(point '(0,0)', point '(1,1)');",
1593    pg("functions-geometry.html")
1594  );
1595  f!(
1596    "center",
1597    "center(box|circle) -> point",
1598    "Centroid of a geometric object.",
1599    "SELECT center(box '(0,0),(2,2)');",
1600    pg("functions-geometry.html")
1601  );
1602  f!(
1603    "distance",
1604    "<obj> <-> <obj> -- distance operator alias.",
1605    "Distance between two geometric objects.",
1606    "SELECT point '(0,0)' <-> point '(3,4)';",
1607    pg("functions-geometry.html")
1608  );
1609  f!(
1610    "isclosed",
1611    "isclosed(path) -> boolean",
1612    "True when the path is closed.",
1613    "SELECT isclosed(path '((0,0),(1,1))');",
1614    pg("functions-geometry.html")
1615  );
1616  f!(
1617    "isopen",
1618    "isopen(path) -> boolean",
1619    "True when the path is open.",
1620    "SELECT isopen(path '[(0,0),(1,1)]');",
1621    pg("functions-geometry.html")
1622  );
1623  f!(
1624    "npoints",
1625    "npoints(path|polygon) -> int",
1626    "Number of vertices in a path or polygon.",
1627    "SELECT npoints(polygon '((0,0),(1,0),(1,1))');",
1628    pg("functions-geometry.html")
1629  );
1630  f!(
1631    "pclose",
1632    "pclose(path) -> path",
1633    "Convert a path to a closed path.",
1634    "SELECT pclose(path '[(0,0),(1,1)]');",
1635    pg("functions-geometry.html")
1636  );
1637  f!(
1638    "popen",
1639    "popen(path) -> path",
1640    "Convert a path to an open path.",
1641    "SELECT popen(path '((0,0),(1,1))');",
1642    pg("functions-geometry.html")
1643  );
1644  f!(
1645    "radius",
1646    "radius(circle) -> double precision",
1647    "Radius of a circle.",
1648    "SELECT radius(circle '<(0,0),5>');",
1649    pg("functions-geometry.html")
1650  );
1651
1652  // ---- cycle2 round 4: missing core temporal + formatting + reg* + jsonb_set + size fns ----
1653  f!(
1654    "age",
1655    "age(timestamp [, timestamp]) -> interval",
1656    "Time elapsed between two timestamps (defaults to now()::date if second arg omitted).",
1657    "SELECT age(now(), birth) FROM users;",
1658    pg("functions-datetime.html#FUNCTIONS-DATETIME-CURRENT")
1659  );
1660  f!(
1661    "date_part",
1662    "date_part(text, timestamp|interval) -> double precision",
1663    "Extract sub-field. SQL-standard form: `EXTRACT(<field> FROM ...)`.",
1664    "SELECT date_part('year', now());",
1665    pg("functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT")
1666  );
1667  f!(
1668    "date_trunc",
1669    "date_trunc(text, timestamp [, text]) -> timestamp",
1670    "Round timestamp down to a unit (hour/day/week/month/year/...). Optional third arg = time zone.",
1671    "SELECT date_trunc('day', now()), date_trunc('day', now(), 'UTC');",
1672    pg("functions-datetime.html#FUNCTIONS-DATETIME-TRUNC")
1673  );
1674  f!(
1675    "to_char",
1676    "to_char(value, format) -> text",
1677    "Format a number/date/timestamp/interval to text using a pattern (`YYYY-MM-DD`, `FM999.99`, etc).",
1678    "SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS');",
1679    pg("functions-formatting.html")
1680  );
1681  f!(
1682    "to_date",
1683    "to_date(text, text) -> date",
1684    "Parse text to date using a pattern.",
1685    "SELECT to_date('2026-05-30', 'YYYY-MM-DD');",
1686    pg("functions-formatting.html")
1687  );
1688  f!(
1689    "to_number",
1690    "to_number(text, text) -> numeric",
1691    "Parse text to numeric using a pattern.",
1692    "SELECT to_number('$1,234.50', 'L9,999.99');",
1693    pg("functions-formatting.html")
1694  );
1695  f!(
1696    "to_timestamp",
1697    "to_timestamp(text, text) -> timestamptz | to_timestamp(double) -> timestamptz",
1698    "Two forms: pattern-parse text, or convert Unix epoch seconds to timestamptz.",
1699    "SELECT to_timestamp('2026-05-30','YYYY-MM-DD'), to_timestamp(1717000000);",
1700    pg("functions-formatting.html")
1701  );
1702  f!(
1703    "justify_days",
1704    "justify_days(interval) -> interval",
1705    "Roll 30-day blocks into months.",
1706    "SELECT justify_days(interval '90 days');",
1707    pg("functions-datetime.html")
1708  );
1709  f!(
1710    "justify_hours",
1711    "justify_hours(interval) -> interval",
1712    "Roll 24-hour blocks into days.",
1713    "SELECT justify_hours(interval '49 hours');",
1714    pg("functions-datetime.html")
1715  );
1716  f!(
1717    "justify_interval",
1718    "justify_interval(interval) -> interval",
1719    "Apply both day and hour justifications.",
1720    "SELECT justify_interval(interval '1 month 100 days 2 hours');",
1721    pg("functions-datetime.html")
1722  );
1723  f!(
1724    "make_date",
1725    "make_date(year int, month int, day int) -> date",
1726    "Build a date from components.",
1727    "SELECT make_date(2026, 5, 30);",
1728    pg("functions-datetime.html")
1729  );
1730  f!(
1731    "make_time",
1732    "make_time(hour int, min int, sec double precision) -> time",
1733    "Build a time-of-day from components.",
1734    "SELECT make_time(14, 30, 0);",
1735    pg("functions-datetime.html")
1736  );
1737  f!(
1738    "make_timestamp",
1739    "make_timestamp(year, month, day, hour, min, sec) -> timestamp",
1740    "Build a timestamp (no zone) from components.",
1741    "SELECT make_timestamp(2026, 5, 30, 14, 30, 0);",
1742    pg("functions-datetime.html")
1743  );
1744  f!(
1745    "make_timestamptz",
1746    "make_timestamptz(year, month, day, hour, min, sec [, tz]) -> timestamptz",
1747    "Build a timestamptz from components; optional time zone.",
1748    "SELECT make_timestamptz(2026, 5, 30, 14, 30, 0, 'UTC');",
1749    pg("functions-datetime.html")
1750  );
1751  f!(
1752    "make_interval",
1753    "make_interval([years=][, months=][, weeks=][, days=][, hours=][, mins=][, secs=]) -> interval",
1754    "Build an interval from named components.",
1755    "SELECT make_interval(days => 7, hours => 12);",
1756    pg("functions-datetime.html")
1757  );
1758  f!(
1759    "to_regclass",
1760    "to_regclass(text) -> regclass",
1761    "Look up table OID by name. Returns NULL when not found (vs `::regclass` which errors).",
1762    "SELECT to_regclass('public.users') IS NOT NULL;",
1763    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
1764  );
1765  f!(
1766    "to_regtype",
1767    "to_regtype(text) -> regtype",
1768    "Look up type OID by name; NULL when missing.",
1769    "SELECT to_regtype('int4') IS NOT NULL;",
1770    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
1771  );
1772  f!(
1773    "to_regproc",
1774    "to_regproc(text) -> regproc",
1775    "Look up function OID by simple name; NULL when missing.",
1776    "SELECT to_regproc('now') IS NOT NULL;",
1777    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
1778  );
1779  f!(
1780    "to_regprocedure",
1781    "to_regprocedure(text) -> regprocedure",
1782    "Look up function OID by signature `name(arg_types)`; NULL when missing.",
1783    "SELECT to_regprocedure('pg_size_pretty(bigint)') IS NOT NULL;",
1784    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
1785  );
1786  f!(
1787    "jsonb_set",
1788    "jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean]) -> jsonb",
1789    "Set or replace a nested value at `path`. `create_missing` (default true) inserts missing keys.",
1790    "SELECT jsonb_set('{\"a\":1}'::jsonb, '{b,c}', '42'::jsonb, true);",
1791    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
1792  );
1793  f!(
1794    "jsonb_strip_nulls",
1795    "jsonb_strip_nulls(jsonb) -> jsonb",
1796    "Recursively remove all keys with NULL values.",
1797    "SELECT jsonb_strip_nulls('{\"a\":1,\"b\":null}'::jsonb);",
1798    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
1799  );
1800  f!(
1801    "jsonb_pretty",
1802    "jsonb_pretty(jsonb) -> text",
1803    "Pretty-print jsonb with indentation.",
1804    "SELECT jsonb_pretty('{\"a\":[1,2]}'::jsonb);",
1805    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
1806  );
1807  f!(
1808    "pg_size_pretty",
1809    "pg_size_pretty(bigint) -> text",
1810    "Format a byte count as human-readable string (kB / MB / GB / TB).",
1811    "SELECT pg_size_pretty(pg_database_size(current_database()));",
1812    pg("functions-admin.html#FUNCTIONS-ADMIN-DBSIZE")
1813  );
1814  f!(
1815    "pg_database_size",
1816    "pg_database_size(name|oid) -> bigint",
1817    "Total disk usage of a database in bytes.",
1818    "SELECT pg_database_size(current_database());",
1819    pg("functions-admin.html#FUNCTIONS-ADMIN-DBSIZE")
1820  );
1821  f!(
1822    "pg_table_size",
1823    "pg_table_size(regclass) -> bigint",
1824    "Bytes used by table's main fork + FSM/VM (excludes indexes/TOAST).",
1825    "SELECT pg_table_size('users');",
1826    pg("functions-admin.html#FUNCTIONS-ADMIN-DBSIZE")
1827  );
1828  f!(
1829    "pg_indexes_size",
1830    "pg_indexes_size(regclass) -> bigint",
1831    "Total bytes used by all indexes on a table.",
1832    "SELECT pg_indexes_size('users');",
1833    pg("functions-admin.html#FUNCTIONS-ADMIN-DBSIZE")
1834  );
1835  f!(
1836    "pg_relation_size",
1837    "pg_relation_size(regclass [, text]) -> bigint",
1838    "Bytes of a specific fork (main/fsm/vm/init); default main.",
1839    "SELECT pg_relation_size('users', 'main');",
1840    pg("functions-admin.html#FUNCTIONS-ADMIN-DBSIZE")
1841  );
1842  f!(
1843    "pg_total_relation_size",
1844    "pg_total_relation_size(regclass) -> bigint",
1845    "Table + TOAST + every index, in bytes.",
1846    "SELECT pg_total_relation_size('users');",
1847    pg("functions-admin.html#FUNCTIONS-ADMIN-DBSIZE")
1848  );
1849
1850  // ---- cycle2 round 5: basic string fns ----
1851  f!(
1852    "lower",
1853    "lower(text) -> text",
1854    "Lowercase text using collation rules.",
1855    "SELECT lower('HELLO');",
1856    pg("functions-string.html#FUNCTIONS-STRING-SQL")
1857  );
1858  f!(
1859    "upper",
1860    "upper(text) -> text",
1861    "Uppercase text using collation rules.",
1862    "SELECT upper('hello');",
1863    pg("functions-string.html#FUNCTIONS-STRING-SQL")
1864  );
1865  f!(
1866    "initcap",
1867    "initcap(text) -> text",
1868    "Capitalize the first letter of each word.",
1869    "SELECT initcap('hello world');",
1870    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
1871  );
1872  f!(
1873    "left",
1874    "left(text, int) -> text",
1875    "First n characters; negative n drops the last |n| chars.",
1876    "SELECT left('postgres', 4);",
1877    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
1878  );
1879  f!(
1880    "right",
1881    "right(text, int) -> text",
1882    "Last n characters; negative n drops the first |n| chars.",
1883    "SELECT right('postgres', 3);",
1884    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
1885  );
1886  f!(
1887    "substr",
1888    "substr(text, from int [, count int]) -> text",
1889    "PG alias of `SUBSTRING`. 1-based.",
1890    "SELECT substr('postgres', 5, 3);",
1891    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
1892  );
1893  f!(
1894    "trim",
1895    "trim([LEADING|TRAILING|BOTH] [chars] FROM text) -> text",
1896    "Strip leading/trailing chars (default whitespace).",
1897    "SELECT trim(BOTH '\"' FROM '\"hi\"');",
1898    pg("functions-string.html#FUNCTIONS-STRING-SQL")
1899  );
1900  f!(
1901    "translate",
1902    "translate(text, from text, to text) -> text",
1903    "Per-char map: replace each char in `from` with the same-index char in `to`; chars without a mapping are deleted.",
1904    "SELECT translate('12345', '143', 'ax');",
1905    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
1906  );
1907  f!(
1908    "replace",
1909    "replace(text, from text, to text) -> text",
1910    "Replace every literal occurrence of `from` with `to`.",
1911    "SELECT replace('foo bar foo', 'foo', 'baz');",
1912    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
1913  );
1914  f!(
1915    "overlay",
1916    "overlay(text PLACING text FROM int [FOR int]) -> text",
1917    "Substring replacement starting at 1-based position.",
1918    "SELECT overlay('Postgres' PLACING 'SQL' FROM 5);",
1919    pg("functions-string.html#FUNCTIONS-STRING-SQL")
1920  );
1921  f!(
1922    "position",
1923    "position(needle IN haystack) -> int",
1924    "1-based index of needle in haystack; 0 when not found.",
1925    "SELECT position('@' IN 'a@b.com');",
1926    pg("functions-string.html#FUNCTIONS-STRING-SQL")
1927  );
1928  f!(
1929    "octet_length",
1930    "octet_length(text|bytea) -> int",
1931    "Length in bytes (not characters).",
1932    "SELECT octet_length('café');",
1933    pg("functions-string.html#FUNCTIONS-STRING-SQL")
1934  );
1935  f!(
1936    "bit_length",
1937    "bit_length(text|bytea) -> int",
1938    "Length in bits.",
1939    "SELECT bit_length('a');",
1940    pg("functions-string.html#FUNCTIONS-STRING-SQL")
1941  );
1942  f!(
1943    "char_length",
1944    "char_length(text) -> int",
1945    "Length in characters. Alias `character_length`, `length`.",
1946    "SELECT char_length('café');",
1947    pg("functions-string.html#FUNCTIONS-STRING-SQL")
1948  );
1949  f!(
1950    "character_length",
1951    "character_length(text) -> int",
1952    "SQL-standard alias of `char_length`.",
1953    "SELECT character_length('café');",
1954    pg("functions-string.html#FUNCTIONS-STRING-SQL")
1955  );
1956  f!(
1957    "length",
1958    "length(text|bytea) -> int",
1959    "Characters for text; bytes for bytea.",
1960    "SELECT length('café'), length('café'::bytea);",
1961    pg("functions-string.html#FUNCTIONS-STRING-SQL")
1962  );
1963  f!(
1964    "encode",
1965    "encode(bytea, format text) -> text",
1966    "Encode bytes as text: 'base64', 'hex', 'escape'.",
1967    "SELECT encode(sha256('x'::bytea), 'hex');",
1968    pg("functions-binarystring.html")
1969  );
1970  f!(
1971    "decode",
1972    "decode(text, format text) -> bytea",
1973    "Decode text into bytes using a format ('base64', 'hex', 'escape').",
1974    "SELECT decode('48656c6c6f', 'hex');",
1975    pg("functions-binarystring.html")
1976  );
1977  f!(
1978    "ascii",
1979    "ascii(text) -> int",
1980    "Unicode codepoint of the first character.",
1981    "SELECT ascii('A');",
1982    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
1983  );
1984  f!(
1985    "lpad",
1986    "lpad(text, length int [, fill text]) -> text",
1987    "Left-pad to length with `fill` (default space); truncates when text longer than length.",
1988    "SELECT lpad('42', 5, '0');",
1989    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
1990  );
1991  f!(
1992    "rpad",
1993    "rpad(text, length int [, fill text]) -> text",
1994    "Right-pad to length with `fill` (default space).",
1995    "SELECT rpad('x', 5, '-');",
1996    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
1997  );
1998  f!(
1999    "btrim",
2000    "btrim(text [, chars text]) -> text",
2001    "Trim chars from BOTH sides (default whitespace).",
2002    "SELECT btrim(' hello ');",
2003    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
2004  );
2005  f!(
2006    "ltrim",
2007    "ltrim(text [, chars text]) -> text",
2008    "Trim chars from LEADING side.",
2009    "SELECT ltrim('00042', '0');",
2010    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
2011  );
2012  f!(
2013    "rtrim",
2014    "rtrim(text [, chars text]) -> text",
2015    "Trim chars from TRAILING side.",
2016    "SELECT rtrim('hello   ');",
2017    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
2018  );
2019  f!(
2020    "repeat",
2021    "repeat(text, int) -> text",
2022    "Concatenate text n times.",
2023    "SELECT repeat('-', 30);",
2024    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
2025  );
2026  f!(
2027    "reverse",
2028    "reverse(text) -> text",
2029    "Reverse characters (UTF-8 safe).",
2030    "SELECT reverse('abc');",
2031    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
2032  );
2033  f!(
2034    "split_part",
2035    "split_part(text, delim text, field int) -> text",
2036    "Return the nth field after splitting by delim (1-based; negative counts from end since PG14).",
2037    "SELECT split_part('a,b,c', ',', 2);",
2038    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
2039  );
2040  f!(
2041    "starts_with",
2042    "starts_with(text, prefix text) -> boolean",
2043    "True when text begins with prefix. PG14+; SQL-portable equivalent: `text LIKE prefix || '%'`.",
2044    "SELECT starts_with('postgres', 'post');",
2045    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
2046  );
2047  f!(
2048    "concat",
2049    "concat(any [, ...]) -> text",
2050    "Concatenate args as text; NULL args become empty (unlike `||`).",
2051    "SELECT concat('a', NULL, 'b');",
2052    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
2053  );
2054  f!(
2055    "concat_ws",
2056    "concat_ws(sep text, any [, ...]) -> text",
2057    "Concatenate with separator; NULLs skipped.",
2058    "SELECT concat_ws(',', 1, NULL, 'x');",
2059    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
2060  );
2061  f!(
2062    "format",
2063    "format(fmt text, args any ...) -> text",
2064    "printf-style: %s, %I (identifier-quote), %L (literal-quote). Safer than ad-hoc concat for dynamic SQL.",
2065    "SELECT format('SELECT * FROM %I WHERE id = %L', 'users', 42);",
2066    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
2067  );
2068  f!(
2069    "quote_ident",
2070    "quote_ident(text) -> text",
2071    "Double-quote text if needed to use as an SQL identifier. Use for safe dynamic SQL.",
2072    "SELECT quote_ident('users with spaces');",
2073    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
2074  );
2075  f!(
2076    "quote_literal",
2077    "quote_literal(text) -> text",
2078    "Single-quote text into an SQL string literal. NULL in -> empty string; use `quote_nullable` to preserve NULL.",
2079    "SELECT quote_literal('O''Reilly');",
2080    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
2081  );
2082  f!(
2083    "quote_nullable",
2084    "quote_nullable(any) -> text",
2085    "Like `quote_literal` but returns the unquoted text `NULL` for NULL input.",
2086    "SELECT quote_nullable(NULL::text);",
2087    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
2088  );
2089  // ---- cycle2 round 5: array + array<->string fns ----
2090  f!(
2091    "string_to_array",
2092    "string_to_array(text, delim text [, null_string text]) -> text[]",
2093    "Split text by delim into an array. Optional `null_string` makes matching tokens NULL.",
2094    "SELECT string_to_array('a,,c', ',', '');",
2095    pg("functions-array.html")
2096  );
2097  f!(
2098    "array_to_string",
2099    "array_to_string(anyarray, delim text [, null_string text]) -> text",
2100    "Join array elements with delim; NULL elements use `null_string` (or are skipped if omitted).",
2101    "SELECT array_to_string(ARRAY[1,2,3], ',');",
2102    pg("functions-array.html")
2103  );
2104  f!(
2105    "string_agg",
2106    "string_agg(text, delim text [ORDER BY ...]) -> text",
2107    "Aggregate: concat per-group strings with delim. Use ORDER BY for deterministic output.",
2108    "SELECT string_agg(name, ', ' ORDER BY name) FROM users GROUP BY org_id;",
2109    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2110  );
2111  f!(
2112    "array_agg",
2113    "array_agg(any [ORDER BY ...]) -> anyarray",
2114    "Aggregate: collect rows into an array.",
2115    "SELECT array_agg(id ORDER BY created_at) FROM events;",
2116    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2117  );
2118  f!(
2119    "array_append",
2120    "array_append(anyarray, any) -> anyarray",
2121    "Append an element. Same as `arr || elem`.",
2122    "SELECT array_append(ARRAY[1,2], 3);",
2123    pg("functions-array.html")
2124  );
2125  f!(
2126    "array_prepend",
2127    "array_prepend(any, anyarray) -> anyarray",
2128    "Prepend an element. Same as `elem || arr`.",
2129    "SELECT array_prepend(0, ARRAY[1,2]);",
2130    pg("functions-array.html")
2131  );
2132  f!(
2133    "array_cat",
2134    "array_cat(anyarray, anyarray) -> anyarray",
2135    "Concatenate two arrays. Same as `a || b`.",
2136    "SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);",
2137    pg("functions-array.html")
2138  );
2139  f!(
2140    "array_remove",
2141    "array_remove(anyarray, any) -> anyarray",
2142    "Remove every occurrence of a value from a 1-D array.",
2143    "SELECT array_remove(ARRAY[1,2,3,2], 2);",
2144    pg("functions-array.html")
2145  );
2146  f!(
2147    "array_replace",
2148    "array_replace(anyarray, from any, to any) -> anyarray",
2149    "Replace every occurrence of `from` with `to`.",
2150    "SELECT array_replace(ARRAY[1,2,3], 2, 20);",
2151    pg("functions-array.html")
2152  );
2153  f!(
2154    "array_position",
2155    "array_position(anyarray, any [, start int]) -> int",
2156    "1-based index of first occurrence; NULL when absent.",
2157    "SELECT array_position(ARRAY['a','b','c'], 'b');",
2158    pg("functions-array.html")
2159  );
2160  f!(
2161    "array_positions",
2162    "array_positions(anyarray, any) -> int[]",
2163    "All 1-based indexes of a value.",
2164    "SELECT array_positions(ARRAY[1,2,3,2,1], 2);",
2165    pg("functions-array.html")
2166  );
2167  f!(
2168    "array_length",
2169    "array_length(anyarray, dim int) -> int",
2170    "Length along dimension `dim`.",
2171    "SELECT array_length(ARRAY[1,2,3], 1);",
2172    pg("functions-array.html")
2173  );
2174  f!(
2175    "array_lower",
2176    "array_lower(anyarray, dim int) -> int",
2177    "Lower bound (default 1) of dimension.",
2178    "SELECT array_lower(ARRAY[1,2,3], 1);",
2179    pg("functions-array.html")
2180  );
2181  f!(
2182    "array_upper",
2183    "array_upper(anyarray, dim int) -> int",
2184    "Upper bound of dimension.",
2185    "SELECT array_upper(ARRAY[1,2,3], 1);",
2186    pg("functions-array.html")
2187  );
2188  f!(
2189    "array_ndims",
2190    "array_ndims(anyarray) -> int",
2191    "Number of dimensions.",
2192    "SELECT array_ndims(ARRAY[[1,2],[3,4]]);",
2193    pg("functions-array.html")
2194  );
2195  f!(
2196    "array_dims",
2197    "array_dims(anyarray) -> text",
2198    "Text representation of dimensions, e.g. `[1:3]`.",
2199    "SELECT array_dims(ARRAY[[1,2],[3,4]]);",
2200    pg("functions-array.html")
2201  );
2202  f!(
2203    "cardinality",
2204    "cardinality(anyarray) -> int",
2205    "Total element count across all dimensions.",
2206    "SELECT cardinality(ARRAY[[1,2],[3,4]]);",
2207    pg("functions-array.html")
2208  );
2209  f!(
2210    "unnest",
2211    "unnest(anyarray [, anyarray ...]) -> setof element",
2212    "Expand array(s) into rows. Multi-arg form pads shorter arrays with NULL.",
2213    "SELECT * FROM unnest(ARRAY[1,2,3]) AS t(v);",
2214    pg("functions-array.html")
2215  );
2216  f!(
2217    "array_fill",
2218    "array_fill(elem any, dims int[] [, lowers int[]]) -> anyarray",
2219    "Fill an array of given dimensions with `elem`.",
2220    "SELECT array_fill(0, ARRAY[3,3]);",
2221    pg("functions-array.html")
2222  );
2223  f!(
2224    "generate_subscripts",
2225    "generate_subscripts(anyarray, dim int [, reverse boolean]) -> setof int",
2226    "Set of valid subscripts along a dimension.",
2227    "SELECT i, a[i] FROM (SELECT ARRAY['a','b','c'] AS a) s, generate_subscripts(a, 1) AS i;",
2228    pg("functions-array.html")
2229  );
2230  // ---- cycle2 round 5: json/jsonb fns ----
2231  f!(
2232    "jsonb_path_query",
2233    "jsonb_path_query(jsonb, jsonpath [, vars jsonb [, silent boolean]]) -> setof jsonb",
2234    "JSONPath query returning each match as a row.",
2235    "SELECT jsonb_path_query('{\"a\":[1,2,3]}'::jsonb, '$.a[*]');",
2236    pg("functions-json.html#FUNCTIONS-SQLJSON-PATH")
2237  );
2238  f!(
2239    "jsonb_path_query_array",
2240    "jsonb_path_query_array(jsonb, jsonpath [, ...]) -> jsonb",
2241    "JSONPath query returning all matches as a JSON array.",
2242    "SELECT jsonb_path_query_array(j, '$.tags[*]');",
2243    pg("functions-json.html#FUNCTIONS-SQLJSON-PATH")
2244  );
2245  f!(
2246    "jsonb_path_query_first",
2247    "jsonb_path_query_first(jsonb, jsonpath [, ...]) -> jsonb",
2248    "Return only the first JSONPath match.",
2249    "SELECT jsonb_path_query_first(j, '$.name');",
2250    pg("functions-json.html#FUNCTIONS-SQLJSON-PATH")
2251  );
2252  f!(
2253    "jsonb_path_exists",
2254    "jsonb_path_exists(jsonb, jsonpath [, ...]) -> boolean",
2255    "True when JSONPath matches at least one value.",
2256    "SELECT jsonb_path_exists(j, '$.id');",
2257    pg("functions-json.html#FUNCTIONS-SQLJSON-PATH")
2258  );
2259  f!(
2260    "jsonb_path_match",
2261    "jsonb_path_match(jsonb, jsonpath [, ...]) -> boolean",
2262    "Evaluate a JSONPath that yields a single boolean.",
2263    "SELECT jsonb_path_match(j, '$.age > 18');",
2264    pg("functions-json.html#FUNCTIONS-SQLJSON-PATH")
2265  );
2266  f!(
2267    "jsonb_array_length",
2268    "jsonb_array_length(jsonb) -> int",
2269    "Length of a JSON array; errors on non-array.",
2270    "SELECT jsonb_array_length('[1,2,3]'::jsonb);",
2271    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
2272  );
2273  f!(
2274    "jsonb_object_keys",
2275    "jsonb_object_keys(jsonb) -> setof text",
2276    "Top-level keys of a JSON object.",
2277    "SELECT jsonb_object_keys('{\"a\":1,\"b\":2}'::jsonb);",
2278    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
2279  );
2280  f!(
2281    "jsonb_each",
2282    "jsonb_each(jsonb) -> setof (key text, value jsonb)",
2283    "Iterate object's (key, value) pairs.",
2284    "SELECT * FROM jsonb_each('{\"a\":1,\"b\":2}'::jsonb);",
2285    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
2286  );
2287  f!(
2288    "jsonb_each_text",
2289    "jsonb_each_text(jsonb) -> setof (key text, value text)",
2290    "Same as `jsonb_each` but values cast to text.",
2291    "SELECT * FROM jsonb_each_text('{\"a\":1}'::jsonb);",
2292    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
2293  );
2294  f!(
2295    "jsonb_typeof",
2296    "jsonb_typeof(jsonb) -> text",
2297    "JSON type: 'object', 'array', 'string', 'number', 'boolean', 'null'.",
2298    "SELECT jsonb_typeof('[1,2,3]'::jsonb);",
2299    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
2300  );
2301  f!(
2302    "json_object_agg",
2303    "json_object_agg(key, value) -> json",
2304    "Aggregate: build a JSON object from key/value pairs.",
2305    "SELECT json_object_agg(name, value) FROM kv;",
2306    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2307  );
2308  f!(
2309    "jsonb_object_agg",
2310    "jsonb_object_agg(key, value) -> jsonb",
2311    "Aggregate: build a jsonb object from key/value pairs.",
2312    "SELECT jsonb_object_agg(name, value) FROM kv;",
2313    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2314  );
2315  f!(
2316    "json_agg",
2317    "json_agg(any [ORDER BY ...]) -> json",
2318    "Aggregate: collect rows as a JSON array.",
2319    "SELECT json_agg(row(id, name) ORDER BY id) FROM users;",
2320    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2321  );
2322  f!(
2323    "jsonb_agg",
2324    "jsonb_agg(any [ORDER BY ...]) -> jsonb",
2325    "Aggregate: collect rows as a jsonb array.",
2326    "SELECT jsonb_agg(t ORDER BY id) FROM (SELECT id, name FROM users) t;",
2327    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2328  );
2329  f!(
2330    "row_to_json",
2331    "row_to_json(record [, pretty boolean]) -> json",
2332    "Serialize a row/composite to JSON.",
2333    "SELECT row_to_json(u) FROM users u LIMIT 1;",
2334    pg("functions-json.html#FUNCTIONS-JSON-CREATION-TABLE")
2335  );
2336  f!(
2337    "json_build_object",
2338    "json_build_object(any ...) -> json",
2339    "Alternating key/value args, JSON object out.",
2340    "SELECT json_build_object('id', 1, 'name', 'a');",
2341    pg("functions-json.html#FUNCTIONS-JSON-CREATION-TABLE")
2342  );
2343  f!(
2344    "jsonb_build_object",
2345    "jsonb_build_object(any ...) -> jsonb",
2346    "Same as `json_build_object` but jsonb.",
2347    "SELECT jsonb_build_object('id', 1, 'name', 'a');",
2348    pg("functions-json.html#FUNCTIONS-JSON-CREATION-TABLE")
2349  );
2350  f!(
2351    "json_build_array",
2352    "json_build_array(any ...) -> json",
2353    "Variadic args to a JSON array.",
2354    "SELECT json_build_array(1, 'x', true);",
2355    pg("functions-json.html#FUNCTIONS-JSON-CREATION-TABLE")
2356  );
2357  f!(
2358    "jsonb_build_array",
2359    "jsonb_build_array(any ...) -> jsonb",
2360    "Variadic args to a jsonb array.",
2361    "SELECT jsonb_build_array(1, 'x', true);",
2362    pg("functions-json.html#FUNCTIONS-JSON-CREATION-TABLE")
2363  );
2364
2365  // ---- cycle2 round 6: math fns ----
2366  f!("abs", "abs(numeric|int|...) -> same", "Absolute value.", "SELECT abs(-12);", pg("functions-math.html"));
2367  f!(
2368    "ceiling",
2369    "ceiling(numeric) -> numeric",
2370    "Smallest integer >= argument (alias of `ceil`).",
2371    "SELECT ceiling(1.2);",
2372    pg("functions-math.html")
2373  );
2374  f!(
2375    "floor",
2376    "floor(numeric) -> numeric",
2377    "Largest integer <= argument.",
2378    "SELECT floor(1.9);",
2379    pg("functions-math.html")
2380  );
2381  f!(
2382    "round",
2383    "round(numeric [, int]) -> numeric",
2384    "Round half-away-from-zero to N decimals (default 0).",
2385    "SELECT round(2.567, 2);",
2386    pg("functions-math.html")
2387  );
2388  f!(
2389    "trunc",
2390    "trunc(numeric [, int]) -> numeric",
2391    "Truncate toward zero to N decimals.",
2392    "SELECT trunc(2.567, 1);",
2393    pg("functions-math.html")
2394  );
2395  f!(
2396    "div",
2397    "div(numerator, denominator) -> numeric",
2398    "Integer division (truncated toward zero).",
2399    "SELECT div(7, 2);",
2400    pg("functions-math.html")
2401  );
2402  f!(
2403    "mod",
2404    "mod(a, b) -> same",
2405    "Remainder of `a / b`. Sign follows the dividend.",
2406    "SELECT mod(10, 3);",
2407    pg("functions-math.html")
2408  );
2409  f!(
2410    "power",
2411    "power(base, exp) -> double precision|numeric",
2412    "Exponentiation; alias of `^`.",
2413    "SELECT power(2, 10);",
2414    pg("functions-math.html")
2415  );
2416  f!(
2417    "log",
2418    "log(b numeric, x numeric) -> numeric | log(numeric) -> numeric",
2419    "Two-arg: log of x in base b. One-arg: base 10.",
2420    "SELECT log(2.0, 8.0), log(100);",
2421    pg("functions-math.html")
2422  );
2423  f!("sqrt", "sqrt(numeric|double precision) -> same", "Square root.", "SELECT sqrt(144);", pg("functions-math.html"));
2424  f!(
2425    "sign",
2426    "sign(numeric) -> numeric",
2427    "-1, 0, or 1.",
2428    "SELECT sign(-3), sign(0), sign(5);",
2429    pg("functions-math.html")
2430  );
2431  f!(
2432    "cos",
2433    "cos(double precision) -> double precision",
2434    "Cosine (radians).",
2435    "SELECT cos(0);",
2436    pg("functions-math.html")
2437  );
2438  f!(
2439    "tan",
2440    "tan(double precision) -> double precision",
2441    "Tangent (radians).",
2442    "SELECT tan(pi()/4);",
2443    pg("functions-math.html")
2444  );
2445  f!(
2446    "asin",
2447    "asin(double precision) -> double precision",
2448    "Inverse sine; result in radians.",
2449    "SELECT asin(1);",
2450    pg("functions-math.html")
2451  );
2452  f!(
2453    "acos",
2454    "acos(double precision) -> double precision",
2455    "Inverse cosine; result in radians.",
2456    "SELECT acos(0);",
2457    pg("functions-math.html")
2458  );
2459  f!(
2460    "atan",
2461    "atan(double precision) -> double precision",
2462    "Inverse tangent; result in radians.",
2463    "SELECT atan(1);",
2464    pg("functions-math.html")
2465  );
2466  f!(
2467    "atan2",
2468    "atan2(y double precision, x double precision) -> double precision",
2469    "Two-arg arctan; quadrant-aware.",
2470    "SELECT atan2(1, 1);",
2471    pg("functions-math.html")
2472  );
2473  f!(
2474    "degrees",
2475    "degrees(double precision) -> double precision",
2476    "Radians -> degrees.",
2477    "SELECT degrees(pi());",
2478    pg("functions-math.html")
2479  );
2480  f!(
2481    "radians",
2482    "radians(double precision) -> double precision",
2483    "Degrees -> radians.",
2484    "SELECT radians(180);",
2485    pg("functions-math.html")
2486  );
2487  f!(
2488    "random",
2489    "random() -> double precision",
2490    "Uniform [0,1). NOT cryptographically secure -- use `gen_random_uuid()`/`pgcrypto` for tokens.",
2491    "SELECT random();",
2492    pg("functions-math.html")
2493  );
2494  f!(
2495    "setseed",
2496    "setseed(double precision) -> void",
2497    "Seed `random()` with a value in [-1,1] for reproducibility within the session.",
2498    "SELECT setseed(0.42); SELECT random();",
2499    pg("functions-math.html")
2500  );
2501  f!(
2502    "scale",
2503    "scale(numeric) -> int",
2504    "Display scale (digits after decimal point).",
2505    "SELECT scale(1.23000);",
2506    pg("functions-math.html")
2507  );
2508  f!(
2509    "min_scale",
2510    "min_scale(numeric) -> int",
2511    "Minimum scale needed to represent the value exactly (no trailing zeros).",
2512    "SELECT min_scale(1.23000);",
2513    pg("functions-math.html")
2514  );
2515  f!(
2516    "trim_scale",
2517    "trim_scale(numeric) -> numeric",
2518    "Strip trailing zeros from the fractional part.",
2519    "SELECT trim_scale(1.23000);",
2520    pg("functions-math.html")
2521  );
2522
2523  // ---- cycle2 round 6: regex fns ----
2524  f!(
2525    "regexp_match",
2526    "regexp_match(text, pattern [, flags]) -> text[]",
2527    "Return the first match's capture groups as an array; NULL when no match.",
2528    "SELECT regexp_match('foo123bar', '([a-z]+)(\\d+)');",
2529    pg("functions-matching.html#FUNCTIONS-POSIX-REGEXP")
2530  );
2531  f!(
2532    "regexp_matches",
2533    "regexp_matches(text, pattern [, flags]) -> setof text[]",
2534    "All matches as rows. Flag `g` returns every match (else just the first).",
2535    "SELECT regexp_matches('aabbcc', '([a-z])\\1', 'g');",
2536    pg("functions-matching.html#FUNCTIONS-POSIX-REGEXP")
2537  );
2538  f!(
2539    "regexp_replace",
2540    "regexp_replace(text, pattern, replacement [, flags]) -> text",
2541    "Replace matches. Use `g` flag for global, `i` for case-insensitive.",
2542    "SELECT regexp_replace('Hello world', 'world', 'PG', 'gi');",
2543    pg("functions-matching.html#FUNCTIONS-POSIX-REGEXP")
2544  );
2545  f!(
2546    "regexp_split_to_array",
2547    "regexp_split_to_array(text, pattern [, flags]) -> text[]",
2548    "Split text on regex matches into an array.",
2549    "SELECT regexp_split_to_array('a, b ,c', '\\s*,\\s*');",
2550    pg("functions-matching.html#FUNCTIONS-POSIX-REGEXP")
2551  );
2552  f!(
2553    "regexp_split_to_table",
2554    "regexp_split_to_table(text, pattern [, flags]) -> setof text",
2555    "Split text on regex matches into rows.",
2556    "SELECT regexp_split_to_table('a;b;c', ';');",
2557    pg("functions-matching.html#FUNCTIONS-POSIX-REGEXP")
2558  );
2559  f!(
2560    "regexp_count",
2561    "regexp_count(text, pattern [, start int [, flags text]]) -> int",
2562    "Count matches (PG15+).",
2563    "SELECT regexp_count('abcabc', 'b');",
2564    pg("functions-matching.html#FUNCTIONS-POSIX-REGEXP")
2565  );
2566  f!(
2567    "regexp_substr",
2568    "regexp_substr(text, pattern [, start int [, n int [, flags text [, subexpr int]]]]) -> text",
2569    "Return the nth match (PG15+).",
2570    "SELECT regexp_substr('a1 b2 c3', '[a-z]\\d');",
2571    pg("functions-matching.html#FUNCTIONS-POSIX-REGEXP")
2572  );
2573
2574  // ---- cycle2 round 6: fts fns ----
2575  f!(
2576    "to_tsvector",
2577    "to_tsvector([config regconfig,] text) -> tsvector",
2578    "Tokenize + normalize text into a tsvector (default config: `default_text_search_config`).",
2579    "SELECT to_tsvector('english', 'The quick brown fox');",
2580    pg("functions-textsearch.html")
2581  );
2582  f!(
2583    "to_tsquery",
2584    "to_tsquery([config,] text) -> tsquery",
2585    "Parse `& | ! <-> ()`-style query into tsquery. Errors on operator chars in raw user input -- prefer `websearch_to_tsquery` for that.",
2586    "SELECT to_tsquery('english', 'fox & jump:*');",
2587    pg("functions-textsearch.html")
2588  );
2589  f!(
2590    "plainto_tsquery",
2591    "plainto_tsquery([config,] text) -> tsquery",
2592    "Treat input as plain words AND-joined; ignores operators.",
2593    "SELECT plainto_tsquery('english', 'quick fox');",
2594    pg("functions-textsearch.html")
2595  );
2596  f!(
2597    "phraseto_tsquery",
2598    "phraseto_tsquery([config,] text) -> tsquery",
2599    "Like `plainto_tsquery` but words joined with phrase distance operator `<->`.",
2600    "SELECT phraseto_tsquery('english', 'quick brown fox');",
2601    pg("functions-textsearch.html")
2602  );
2603  f!(
2604    "websearch_to_tsquery",
2605    "websearch_to_tsquery([config,] text) -> tsquery",
2606    "Google-style: \"quoted phrases\", -minus, OR. Safe for raw user input.",
2607    "SELECT websearch_to_tsquery('english', '\"quick fox\" OR -dog');",
2608    pg("functions-textsearch.html")
2609  );
2610  f!(
2611    "ts_rank",
2612    "ts_rank([weights,] tsvector, tsquery [, normalization int]) -> real",
2613    "Rank by term frequency. Default weights {0.1, 0.2, 0.4, 1.0} for D,C,B,A.",
2614    "SELECT ts_rank(doc, q) FROM articles, websearch_to_tsquery('postgres') q WHERE doc @@ q ORDER BY 1 DESC;",
2615    pg("functions-textsearch.html")
2616  );
2617  f!(
2618    "ts_rank_cd",
2619    "ts_rank_cd([weights,] tsvector, tsquery [, normalization int]) -> real",
2620    "Cover-density rank (rewards lexeme proximity).",
2621    "SELECT ts_rank_cd(doc, q) FROM articles, q;",
2622    pg("functions-textsearch.html")
2623  );
2624  f!(
2625    "ts_headline",
2626    "ts_headline([config,] text|tsvector, tsquery [, options text]) -> text",
2627    "Highlight matching lexemes in text snippet.",
2628    "SELECT ts_headline('english', body, q, 'MaxFragments=3') FROM articles, websearch_to_tsquery('pg') q;",
2629    pg("functions-textsearch.html")
2630  );
2631  f!(
2632    "numnode",
2633    "numnode(tsquery) -> int",
2634    "Number of nodes in a tsquery (for query-complexity checks).",
2635    "SELECT numnode('a & b'::tsquery);",
2636    pg("functions-textsearch.html")
2637  );
2638  f!(
2639    "queries_to_xml",
2640    "queries_to_xml(tableset_query text [, ...]) -> xml",
2641    "Run query and return result-set as XML. Companion of `query_to_xml`.",
2642    "SELECT queries_to_xml('SELECT * FROM t;', true, false, '');",
2643    pg("functions-xml.html#FUNCTIONS-XML-MAPPING")
2644  );
2645
2646  // ---- cycle2 round 6: range/multirange fns ----
2647  f!(
2648    "isempty",
2649    "isempty(anyrange|anymultirange) -> boolean",
2650    "True when range/multirange covers zero elements.",
2651    "SELECT isempty('[5,5)'::int4range);",
2652    pg("functions-range.html")
2653  );
2654  f!(
2655    "lower_inc",
2656    "lower_inc(anyrange|anymultirange) -> boolean",
2657    "True when the lower bound is inclusive (e.g. `[1,5)` -> true).",
2658    "SELECT lower_inc('[1,5)'::int4range);",
2659    pg("functions-range.html")
2660  );
2661  f!(
2662    "upper_inc",
2663    "upper_inc(anyrange|anymultirange) -> boolean",
2664    "True when the upper bound is inclusive.",
2665    "SELECT upper_inc('[1,5]'::int4range);",
2666    pg("functions-range.html")
2667  );
2668  f!(
2669    "range_merge",
2670    "range_merge(anyrange, anyrange) -> anyrange | range_merge(anymultirange) -> anyrange",
2671    "Smallest range covering all inputs.",
2672    "SELECT range_merge('[1,5)'::int4range, '[10,20)'::int4range);",
2673    pg("functions-range.html")
2674  );
2675  f!(
2676    "int4range",
2677    "int4range(lower int [, upper int [, bounds text]]) -> int4range",
2678    "Construct int4range; `bounds` like '[)' (default) / '(]' / '[]' / '()'.",
2679    "SELECT int4range(1, 10, '[]');",
2680    pg("rangetypes.html")
2681  );
2682  f!(
2683    "int8range",
2684    "int8range(lower bigint, upper bigint [, bounds text]) -> int8range",
2685    "Construct int8range.",
2686    "SELECT int8range(1, 10);",
2687    pg("rangetypes.html")
2688  );
2689  f!(
2690    "numrange",
2691    "numrange(lower numeric, upper numeric [, bounds text]) -> numrange",
2692    "Construct numeric range.",
2693    "SELECT numrange(0.0, 1.0);",
2694    pg("rangetypes.html")
2695  );
2696  f!(
2697    "tsrange",
2698    "tsrange(lower timestamp, upper timestamp [, bounds]) -> tsrange",
2699    "Construct timestamp range.",
2700    "SELECT tsrange('2026-01-01', '2027-01-01');",
2701    pg("rangetypes.html")
2702  );
2703  f!(
2704    "tstzrange",
2705    "tstzrange(lower timestamptz, upper timestamptz [, bounds]) -> tstzrange",
2706    "Construct timestamptz range.",
2707    "SELECT tstzrange(now(), now() + interval '1 day');",
2708    pg("rangetypes.html")
2709  );
2710  f!(
2711    "daterange",
2712    "daterange(lower date, upper date [, bounds]) -> daterange",
2713    "Construct date range.",
2714    "SELECT daterange('2026-01-01', '2027-01-01');",
2715    pg("rangetypes.html")
2716  );
2717  f!(
2718    "multirange",
2719    "multirange(VARIADIC anyrange[]) -> anymultirange",
2720    "Construct multirange from ranges (PG14+).",
2721    "SELECT multirange('[1,5)'::int4range, '[10,20)'::int4range);",
2722    pg("rangetypes.html")
2723  );
2724  f!(
2725    "range_agg",
2726    "range_agg(anyrange) -> anymultirange",
2727    "Aggregate ranges into a multirange (PG14+).",
2728    "SELECT range_agg(period) FROM bookings WHERE room_id = 1;",
2729    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2730  );
2731
2732  // ---- cycle2 round 7: aggregate + window fns ----
2733  f!(
2734    "sum",
2735    "sum(numeric|interval|...) -> same",
2736    "Aggregate sum; NULL ignored. Result type widens (int->bigint, bigint->numeric).",
2737    "SELECT sum(amount) FROM orders;",
2738    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2739  );
2740  f!(
2741    "avg",
2742    "avg(numeric|...) -> numeric|double precision|interval",
2743    "Arithmetic mean.",
2744    "SELECT avg(price) FROM products;",
2745    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2746  );
2747  f!(
2748    "count",
2749    "count(* | expr) -> bigint",
2750    "Row count (`count(*)`) or non-NULL value count (`count(expr)`).",
2751    "SELECT count(*), count(email) FROM users;",
2752    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2753  );
2754  f!(
2755    "min",
2756    "min(any) -> same",
2757    "Smallest non-NULL value.",
2758    "SELECT min(created_at) FROM events;",
2759    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2760  );
2761  f!(
2762    "max",
2763    "max(any) -> same",
2764    "Largest non-NULL value.",
2765    "SELECT max(price) FROM products;",
2766    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2767  );
2768  f!(
2769    "bool_and",
2770    "bool_and(boolean) -> boolean",
2771    "True when every non-NULL row is true.",
2772    "SELECT bool_and(active) FROM users;",
2773    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2774  );
2775  f!(
2776    "bool_or",
2777    "bool_or(boolean) -> boolean",
2778    "True when any row is true.",
2779    "SELECT bool_or(error) FROM logs;",
2780    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2781  );
2782  f!(
2783    "every",
2784    "every(boolean) -> boolean",
2785    "SQL-standard alias of `bool_and`.",
2786    "SELECT every(active) FROM users;",
2787    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2788  );
2789  f!(
2790    "bit_and",
2791    "bit_and(int|bit) -> same",
2792    "Bitwise AND across rows.",
2793    "SELECT bit_and(flags) FROM perms;",
2794    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2795  );
2796  f!(
2797    "bit_or",
2798    "bit_or(int|bit) -> same",
2799    "Bitwise OR across rows.",
2800    "SELECT bit_or(flags) FROM perms;",
2801    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
2802  );
2803  f!(
2804    "stddev",
2805    "stddev(numeric) -> numeric|double precision",
2806    "Sample standard deviation (alias of `stddev_samp`).",
2807    "SELECT stddev(latency) FROM probes;",
2808    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2809  );
2810  f!(
2811    "stddev_pop",
2812    "stddev_pop(numeric) -> numeric|double precision",
2813    "Population standard deviation.",
2814    "SELECT stddev_pop(latency) FROM probes;",
2815    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2816  );
2817  f!(
2818    "stddev_samp",
2819    "stddev_samp(numeric) -> numeric|double precision",
2820    "Sample standard deviation (Bessel-corrected).",
2821    "SELECT stddev_samp(latency) FROM probes;",
2822    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2823  );
2824  f!(
2825    "variance",
2826    "variance(numeric) -> numeric|double precision",
2827    "Sample variance (alias of `var_samp`).",
2828    "SELECT variance(latency) FROM probes;",
2829    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2830  );
2831  f!(
2832    "var_pop",
2833    "var_pop(numeric) -> numeric|double precision",
2834    "Population variance.",
2835    "SELECT var_pop(latency) FROM probes;",
2836    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2837  );
2838  f!(
2839    "var_samp",
2840    "var_samp(numeric) -> numeric|double precision",
2841    "Sample variance.",
2842    "SELECT var_samp(latency) FROM probes;",
2843    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2844  );
2845  f!(
2846    "covar_pop",
2847    "covar_pop(y, x) -> double precision",
2848    "Population covariance.",
2849    "SELECT covar_pop(y, x) FROM xy;",
2850    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2851  );
2852  f!(
2853    "covar_samp",
2854    "covar_samp(y, x) -> double precision",
2855    "Sample covariance.",
2856    "SELECT covar_samp(y, x) FROM xy;",
2857    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2858  );
2859  f!(
2860    "corr",
2861    "corr(y, x) -> double precision",
2862    "Pearson correlation coefficient.",
2863    "SELECT corr(y, x) FROM xy;",
2864    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2865  );
2866  f!(
2867    "regr_slope",
2868    "regr_slope(y, x) -> double precision",
2869    "Slope of least-squares-fit linear regression.",
2870    "SELECT regr_slope(rev, day) FROM daily;",
2871    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2872  );
2873  f!(
2874    "regr_intercept",
2875    "regr_intercept(y, x) -> double precision",
2876    "Intercept of linear regression.",
2877    "SELECT regr_intercept(rev, day) FROM daily;",
2878    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2879  );
2880  f!(
2881    "regr_r2",
2882    "regr_r2(y, x) -> double precision",
2883    "Coefficient of determination.",
2884    "SELECT regr_r2(rev, day) FROM daily;",
2885    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2886  );
2887  f!(
2888    "regr_count",
2889    "regr_count(y, x) -> bigint",
2890    "Rows with non-NULL y AND x.",
2891    "SELECT regr_count(y, x) FROM xy;",
2892    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2893  );
2894  f!(
2895    "regr_avgx",
2896    "regr_avgx(y, x) -> double precision",
2897    "Average of x for rows where both y and x are non-NULL.",
2898    "SELECT regr_avgx(y, x) FROM xy;",
2899    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2900  );
2901  f!(
2902    "regr_avgy",
2903    "regr_avgy(y, x) -> double precision",
2904    "Average of y for rows where both y and x are non-NULL.",
2905    "SELECT regr_avgy(y, x) FROM xy;",
2906    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2907  );
2908  f!(
2909    "regr_sxx",
2910    "regr_sxx(y, x) -> double precision",
2911    "Sum of squares of x.",
2912    "SELECT regr_sxx(y, x) FROM xy;",
2913    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2914  );
2915  f!(
2916    "regr_syy",
2917    "regr_syy(y, x) -> double precision",
2918    "Sum of squares of y.",
2919    "SELECT regr_syy(y, x) FROM xy;",
2920    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2921  );
2922  f!(
2923    "regr_sxy",
2924    "regr_sxy(y, x) -> double precision",
2925    "Sum of products (x_i * y_i).",
2926    "SELECT regr_sxy(y, x) FROM xy;",
2927    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE")
2928  );
2929  f!(
2930    "mode",
2931    "mode() WITHIN GROUP (ORDER BY <expr>) -> same",
2932    "Ordered-set aggregate: most common value.",
2933    "SELECT mode() WITHIN GROUP (ORDER BY status) FROM tickets;",
2934    pg("functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE")
2935  );
2936  f!(
2937    "percentile_cont",
2938    "percentile_cont(fraction) WITHIN GROUP (ORDER BY expr) -> double precision|interval",
2939    "Continuous percentile (linear interp). Common: median = percentile_cont(0.5).",
2940    "SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;",
2941    pg("functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE")
2942  );
2943  f!(
2944    "percentile_disc",
2945    "percentile_disc(fraction) WITHIN GROUP (ORDER BY expr) -> same",
2946    "Discrete percentile (picks an actual row value).",
2947    "SELECT percentile_disc(0.95) WITHIN GROUP (ORDER BY latency) FROM probes;",
2948    pg("functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE")
2949  );
2950  f!(
2951    "rank",
2952    "rank() OVER (...) -> bigint",
2953    "Window: 1-based rank with gaps for ties.",
2954    "SELECT rank() OVER (PARTITION BY org ORDER BY score DESC) FROM scores;",
2955    pg("functions-window.html")
2956  );
2957  f!(
2958    "dense_rank",
2959    "dense_rank() OVER (...) -> bigint",
2960    "Window: rank without gaps.",
2961    "SELECT dense_rank() OVER (ORDER BY score DESC) FROM scores;",
2962    pg("functions-window.html")
2963  );
2964  f!(
2965    "percent_rank",
2966    "percent_rank() OVER (...) -> double precision",
2967    "Window: (rank - 1) / (n - 1).",
2968    "SELECT percent_rank() OVER (ORDER BY score) FROM scores;",
2969    pg("functions-window.html")
2970  );
2971  f!(
2972    "cume_dist",
2973    "cume_dist() OVER (...) -> double precision",
2974    "Window: cumulative distribution = rows preceding-or-peer / total rows.",
2975    "SELECT cume_dist() OVER (ORDER BY score) FROM scores;",
2976    pg("functions-window.html")
2977  );
2978  f!(
2979    "row_number",
2980    "row_number() OVER (...) -> bigint",
2981    "Window: monotonically increasing row counter per partition.",
2982    "SELECT row_number() OVER (PARTITION BY user_id ORDER BY ts) FROM events;",
2983    pg("functions-window.html")
2984  );
2985  f!(
2986    "ntile",
2987    "ntile(n int) OVER (...) -> int",
2988    "Window: assign rows to N equal-sized buckets.",
2989    "SELECT ntile(4) OVER (ORDER BY score) FROM scores;",
2990    pg("functions-window.html")
2991  );
2992  f!(
2993    "lag",
2994    "lag(expr [, offset int [, default any]]) OVER (...) -> same",
2995    "Window: value from row offset rows BEFORE the current one (default 1).",
2996    "SELECT ts, lag(ts) OVER (PARTITION BY uid ORDER BY ts) FROM events;",
2997    pg("functions-window.html")
2998  );
2999  f!(
3000    "lead",
3001    "lead(expr [, offset int [, default any]]) OVER (...) -> same",
3002    "Window: value from row offset rows AFTER the current one.",
3003    "SELECT ts, lead(ts) OVER (PARTITION BY uid ORDER BY ts) FROM events;",
3004    pg("functions-window.html")
3005  );
3006  f!(
3007    "first_value",
3008    "first_value(expr) OVER (...) -> same",
3009    "Window: value at the first row of the window frame.",
3010    "SELECT first_value(price) OVER (PARTITION BY sku ORDER BY ts) FROM prices;",
3011    pg("functions-window.html")
3012  );
3013  f!(
3014    "last_value",
3015    "last_value(expr) OVER (...) -> same",
3016    "Window: value at the last row of the window frame. NB: default frame ends at CURRENT ROW -- use `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` for partition-wide.",
3017    "SELECT last_value(price) OVER (PARTITION BY sku ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM prices;",
3018    pg("functions-window.html")
3019  );
3020  f!(
3021    "nth_value",
3022    "nth_value(expr, n int) OVER (...) -> same",
3023    "Window: value at nth row of the frame (1-based).",
3024    "SELECT nth_value(price, 2) OVER (PARTITION BY sku ORDER BY ts) FROM prices;",
3025    pg("functions-window.html")
3026  );
3027
3028  // ---- cycle2 round 7: conversion fns ----
3029  f!(
3030    "int4",
3031    "int4(any) -> int4",
3032    "Cast to int4 (alias `int`/`integer`).",
3033    "SELECT int4(1.7);",
3034    pg("datatype-numeric.html")
3035  );
3036  f!(
3037    "int8",
3038    "int8(any) -> int8",
3039    "Cast to int8 (alias `bigint`).",
3040    "SELECT int8('12345');",
3041    pg("datatype-numeric.html")
3042  );
3043  f!("int2", "int2(any) -> int2", "Cast to int2 (alias `smallint`).", "SELECT int2(42);", pg("datatype-numeric.html"));
3044  f!(
3045    "float4",
3046    "float4(any) -> float4",
3047    "Cast to float4 (alias `real`).",
3048    "SELECT float4('3.14');",
3049    pg("datatype-numeric.html")
3050  );
3051  f!(
3052    "float8",
3053    "float8(any) -> float8",
3054    "Cast to float8 (alias `double precision`).",
3055    "SELECT float8('3.14');",
3056    pg("datatype-numeric.html")
3057  );
3058
3059  // ---- cycle2 round 7: bit fns ----
3060  f!(
3061    "get_bit",
3062    "get_bit(bytea|bit, int) -> int",
3063    "Read bit at 0-based offset.",
3064    "SELECT get_bit('\\xff'::bytea, 0);",
3065    pg("functions-binarystring.html")
3066  );
3067  f!(
3068    "set_bit",
3069    "set_bit(bytea|bit, int, int) -> same",
3070    "Write bit at offset.",
3071    "SELECT set_bit('\\x00'::bytea, 7, 1);",
3072    pg("functions-binarystring.html")
3073  );
3074  f!(
3075    "get_byte",
3076    "get_byte(bytea, int) -> int",
3077    "Read byte at 0-based offset (0..255).",
3078    "SELECT get_byte('\\xdeadbeef'::bytea, 1);",
3079    pg("functions-binarystring.html")
3080  );
3081  f!(
3082    "set_byte",
3083    "set_byte(bytea, int, int) -> bytea",
3084    "Write byte at offset.",
3085    "SELECT set_byte('\\x00000000'::bytea, 2, 255);",
3086    pg("functions-binarystring.html")
3087  );
3088
3089  // ---- cycle2 round 7: network fns ----
3090  f!(
3091    "host",
3092    "host(inet|cidr) -> text",
3093    "Address only, no netmask.",
3094    "SELECT host('192.168.1.5/24'::inet);",
3095    pg("functions-net.html")
3096  );
3097  f!(
3098    "netmask",
3099    "netmask(inet) -> inet",
3100    "Netmask of an inet value.",
3101    "SELECT netmask('192.168.1.0/24');",
3102    pg("functions-net.html")
3103  );
3104  f!(
3105    "network",
3106    "network(inet) -> cidr",
3107    "Network portion as cidr.",
3108    "SELECT network('192.168.1.5/24');",
3109    pg("functions-net.html")
3110  );
3111  f!(
3112    "set_masklen",
3113    "set_masklen(inet|cidr, int) -> same",
3114    "Set the netmask length without changing the address.",
3115    "SELECT set_masklen('192.168.1.5'::inet, 16);",
3116    pg("functions-net.html")
3117  );
3118  f!(
3119    "inet",
3120    "inet(text) -> inet",
3121    "Cast text to inet (also via `::inet`).",
3122    "SELECT inet('10.0.0.1');",
3123    pg("functions-net.html")
3124  );
3125
3126  // ---- cycle2 round 7: uuid fns ----
3127  f!(
3128    "gen_random_uuid",
3129    "gen_random_uuid() -> uuid",
3130    "Cryptographically-strong random UUID (v4). Built-in since PG13 (no extension needed).",
3131    "SELECT gen_random_uuid();",
3132    pg("functions-uuid.html")
3133  );
3134  f!(
3135    "uuid_generate_v1",
3136    "uuid_generate_v1() -> uuid",
3137    "Time-based UUID v1. Requires `uuid-ossp` extension.",
3138    "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\"; SELECT uuid_generate_v1();",
3139    pg("uuid-ossp.html")
3140  );
3141  f!(
3142    "uuid_generate_v4",
3143    "uuid_generate_v4() -> uuid",
3144    "Random UUID v4 (uuid-ossp). PG13+ has built-in `gen_random_uuid()` -- prefer that.",
3145    "SELECT uuid_generate_v4();",
3146    pg("uuid-ossp.html")
3147  );
3148  f!(
3149    "uuid_generate_v5",
3150    "uuid_generate_v5(namespace uuid, name text) -> uuid",
3151    "Name-based UUID using SHA-1.",
3152    "SELECT uuid_generate_v5(uuid_ns_url(), 'https://example.com');",
3153    pg("uuid-ossp.html")
3154  );
3155
3156  // ---- cycle2 round 7: hash/crypto fns ----
3157  f!(
3158    "sha224",
3159    "sha224(bytea) -> bytea",
3160    "SHA-224 hash.",
3161    "SELECT sha224('abc'::bytea);",
3162    pg("functions-binarystring.html")
3163  );
3164  f!(
3165    "sha256",
3166    "sha256(bytea) -> bytea",
3167    "SHA-256 hash.",
3168    "SELECT encode(sha256('abc'::bytea), 'hex');",
3169    pg("functions-binarystring.html")
3170  );
3171  f!(
3172    "sha384",
3173    "sha384(bytea) -> bytea",
3174    "SHA-384 hash.",
3175    "SELECT sha384('abc'::bytea);",
3176    pg("functions-binarystring.html")
3177  );
3178  f!(
3179    "sha512",
3180    "sha512(bytea) -> bytea",
3181    "SHA-512 hash.",
3182    "SELECT sha512('abc'::bytea);",
3183    pg("functions-binarystring.html")
3184  );
3185  f!(
3186    "hashtext",
3187    "hashtext(text) -> int",
3188    "Internal fast hash of text (not cryptographic).",
3189    "SELECT hashtext('abc');",
3190    pg("functions-binarystring.html")
3191  );
3192  f!(
3193    "crypt",
3194    "crypt(password text, salt text) -> text",
3195    "pgcrypto: hash password using crypt() schemes (bf, md5, ...). Use `gen_salt('bf')` for salt.",
3196    "SELECT crypt('secret', gen_salt('bf'));",
3197    pg("pgcrypto.html")
3198  );
3199  f!(
3200    "gen_salt",
3201    "gen_salt(algorithm text [, iter_count int]) -> text",
3202    "pgcrypto: generate a salt for `crypt()`. Algos: 'bf', 'md5', 'des', 'xdes'.",
3203    "SELECT gen_salt('bf', 12);",
3204    pg("pgcrypto.html")
3205  );
3206  f!(
3207    "encrypt",
3208    "encrypt(data bytea, key bytea, type text) -> bytea",
3209    "pgcrypto: symmetric encrypt. `type` like 'aes' or 'aes-cbc/pad:pkcs'.",
3210    "SELECT encrypt('secret'::bytea, 'key'::bytea, 'aes');",
3211    pg("pgcrypto.html")
3212  );
3213  f!(
3214    "decrypt",
3215    "decrypt(data bytea, key bytea, type text) -> bytea",
3216    "pgcrypto: symmetric decrypt counterpart of `encrypt`.",
3217    "SELECT decrypt(ciphertext, 'key', 'aes');",
3218    pg("pgcrypto.html")
3219  );
3220  f!(
3221    "hmac",
3222    "hmac(data bytea|text, key bytea|text, type text) -> bytea",
3223    "pgcrypto: HMAC. `type` like 'sha256'.",
3224    "SELECT encode(hmac('msg', 'key', 'sha256'), 'hex');",
3225    pg("pgcrypto.html")
3226  );
3227  f!(
3228    "digest",
3229    "digest(data bytea|text, type text) -> bytea",
3230    "pgcrypto: arbitrary digest. `type` like 'sha256', 'sha512', 'md5'.",
3231    "SELECT encode(digest('abc', 'sha256'), 'hex');",
3232    pg("pgcrypto.html")
3233  );
3234
3235  // ---- cycle2 round 8: sequence fns ----
3236  f!(
3237    "nextval",
3238    "nextval(regclass) -> bigint",
3239    "Advance sequence and return the new value. Caches per session per `CACHE` setting.",
3240    "SELECT nextval('users_id_seq');",
3241    pg("functions-sequence.html")
3242  );
3243  f!(
3244    "currval",
3245    "currval(regclass) -> bigint",
3246    "Value most recently returned by `nextval` in this session. Errors if no `nextval` has run yet.",
3247    "SELECT currval('users_id_seq');",
3248    pg("functions-sequence.html")
3249  );
3250  f!(
3251    "lastval",
3252    "lastval() -> bigint",
3253    "Value most recently returned by `nextval` for ANY sequence in this session.",
3254    "SELECT lastval();",
3255    pg("functions-sequence.html")
3256  );
3257  f!(
3258    "setval",
3259    "setval(regclass, bigint [, is_called boolean]) -> bigint",
3260    "Reset sequence. With `is_called`=false the next `nextval` returns the same value.",
3261    "SELECT setval('users_id_seq', 1000, true);",
3262    pg("functions-sequence.html")
3263  );
3264
3265  // ---- cycle2 round 8: advisory lock fns ----
3266  f!(
3267    "pg_advisory_lock",
3268    "pg_advisory_lock(key bigint) -> void | pg_advisory_lock(key1 int, key2 int)",
3269    "Session-level exclusive advisory lock. Blocks until acquired; must `pg_advisory_unlock`.",
3270    "SELECT pg_advisory_lock(hashtext('user_42'));",
3271    pg("functions-admin.html#FUNCTIONS-ADVISORY-LOCKS")
3272  );
3273  f!(
3274    "pg_advisory_lock_shared",
3275    "pg_advisory_lock_shared(key bigint) -> void",
3276    "Session-level SHARED advisory lock -- multiple holders OK.",
3277    "SELECT pg_advisory_lock_shared(42);",
3278    pg("functions-admin.html#FUNCTIONS-ADVISORY-LOCKS")
3279  );
3280  f!(
3281    "pg_advisory_unlock",
3282    "pg_advisory_unlock(key bigint) -> boolean",
3283    "Release one stack-frame of a session advisory lock. True if a frame was released.",
3284    "SELECT pg_advisory_unlock(42);",
3285    pg("functions-admin.html#FUNCTIONS-ADVISORY-LOCKS")
3286  );
3287  f!(
3288    "pg_advisory_unlock_shared",
3289    "pg_advisory_unlock_shared(key bigint) -> boolean",
3290    "Release a shared advisory lock.",
3291    "SELECT pg_advisory_unlock_shared(42);",
3292    pg("functions-admin.html#FUNCTIONS-ADVISORY-LOCKS")
3293  );
3294  f!(
3295    "pg_advisory_unlock_all",
3296    "pg_advisory_unlock_all() -> void",
3297    "Release every session-level advisory lock held by this session.",
3298    "SELECT pg_advisory_unlock_all();",
3299    pg("functions-admin.html#FUNCTIONS-ADVISORY-LOCKS")
3300  );
3301  f!(
3302    "pg_advisory_xact_lock",
3303    "pg_advisory_xact_lock(key bigint) -> void",
3304    "Transaction-scoped exclusive advisory lock -- auto-released at COMMIT/ROLLBACK.",
3305    "BEGIN; SELECT pg_advisory_xact_lock(42); ...; COMMIT;",
3306    pg("functions-admin.html#FUNCTIONS-ADVISORY-LOCKS")
3307  );
3308  f!(
3309    "pg_advisory_xact_lock_shared",
3310    "pg_advisory_xact_lock_shared(key bigint) -> void",
3311    "Transaction-scoped SHARED advisory lock.",
3312    "SELECT pg_advisory_xact_lock_shared(42);",
3313    pg("functions-admin.html#FUNCTIONS-ADVISORY-LOCKS")
3314  );
3315  f!(
3316    "pg_try_advisory_lock",
3317    "pg_try_advisory_lock(key bigint) -> boolean",
3318    "Non-blocking acquire of session advisory lock. False when busy.",
3319    "SELECT pg_try_advisory_lock(42);",
3320    pg("functions-admin.html#FUNCTIONS-ADVISORY-LOCKS")
3321  );
3322  f!(
3323    "pg_try_advisory_lock_shared",
3324    "pg_try_advisory_lock_shared(key bigint) -> boolean",
3325    "Non-blocking shared advisory lock acquire.",
3326    "SELECT pg_try_advisory_lock_shared(42);",
3327    pg("functions-admin.html#FUNCTIONS-ADVISORY-LOCKS")
3328  );
3329  f!(
3330    "pg_try_advisory_xact_lock",
3331    "pg_try_advisory_xact_lock(key bigint) -> boolean",
3332    "Non-blocking transaction-scoped exclusive advisory lock.",
3333    "SELECT pg_try_advisory_xact_lock(42);",
3334    pg("functions-admin.html#FUNCTIONS-ADVISORY-LOCKS")
3335  );
3336  f!(
3337    "pg_try_advisory_xact_lock_shared",
3338    "pg_try_advisory_xact_lock_shared(key bigint) -> boolean",
3339    "Non-blocking transaction-scoped shared advisory lock.",
3340    "SELECT pg_try_advisory_xact_lock_shared(42);",
3341    pg("functions-admin.html#FUNCTIONS-ADVISORY-LOCKS")
3342  );
3343
3344  // ---- cycle2 round 8: misc set-returning + sysadmin fns ----
3345  f!(
3346    "generate_series",
3347    "generate_series(start, stop [, step]) -> setof int|bigint|numeric|timestamp",
3348    "Inclusive series. Step defaults to 1 (or `1 day` for timestamps). Negative step iterates downward.",
3349    "SELECT * FROM generate_series(1, 10);\nSELECT * FROM generate_series(date '2026-01-01', date '2026-12-31', interval '1 month');",
3350    pg("functions-srf.html")
3351  );
3352  f!(
3353    "pg_sleep",
3354    "pg_sleep(seconds double precision) -> void",
3355    "Sleep server-side for N seconds.",
3356    "SELECT pg_sleep(0.5);",
3357    pg("functions-datetime.html#FUNCTIONS-DATETIME-DELAY")
3358  );
3359  f!(
3360    "pg_sleep_for",
3361    "pg_sleep_for(interval) -> void",
3362    "Sleep server-side for an interval.",
3363    "SELECT pg_sleep_for('2 seconds');",
3364    pg("functions-datetime.html#FUNCTIONS-DATETIME-DELAY")
3365  );
3366  f!(
3367    "pg_sleep_until",
3368    "pg_sleep_until(timestamptz) -> void",
3369    "Sleep until a timestamp.",
3370    "SELECT pg_sleep_until(now() + interval '1 minute');",
3371    pg("functions-datetime.html#FUNCTIONS-DATETIME-DELAY")
3372  );
3373  f!(
3374    "pg_cancel_backend",
3375    "pg_cancel_backend(pid int) -> boolean",
3376    "Signal cancel (like Ctrl-C) to a backend pid. Caller must be superuser or the same role.",
3377    "SELECT pg_cancel_backend(12345);",
3378    pg("functions-admin.html#FUNCTIONS-ADMIN-SIGNAL")
3379  );
3380  f!(
3381    "pg_terminate_backend",
3382    "pg_terminate_backend(pid int [, timeout int]) -> boolean",
3383    "Send SIGTERM to a backend pid. Stronger than cancel; closes the connection. Optional timeout (PG14+) waits for confirmation.",
3384    "SELECT pg_terminate_backend(12345);",
3385    pg("functions-admin.html#FUNCTIONS-ADMIN-SIGNAL")
3386  );
3387  f!(
3388    "pg_stat_reset",
3389    "pg_stat_reset() -> void",
3390    "Reset every cumulative stats counter in the current DB.",
3391    "SELECT pg_stat_reset();",
3392    pg("monitoring-stats.html#MONITORING-STATS-FUNCTIONS")
3393  );
3394  f!(
3395    "pg_stat_reset_shared",
3396    "pg_stat_reset_shared(target text) -> void",
3397    "Reset a specific cluster-wide stats target ('bgwriter', 'archiver', 'wal', ...).",
3398    "SELECT pg_stat_reset_shared('bgwriter');",
3399    pg("monitoring-stats.html#MONITORING-STATS-FUNCTIONS")
3400  );
3401  f!(
3402    "pg_stat_reset_single_table_counters",
3403    "pg_stat_reset_single_table_counters(regclass) -> void",
3404    "Reset stats for one table only.",
3405    "SELECT pg_stat_reset_single_table_counters('users'::regclass);",
3406    pg("monitoring-stats.html#MONITORING-STATS-FUNCTIONS")
3407  );
3408  f!(
3409    "pg_stat_reset_single_function_counters",
3410    "pg_stat_reset_single_function_counters(regprocedure) -> void",
3411    "Reset stats for one function only.",
3412    "SELECT pg_stat_reset_single_function_counters('now()'::regprocedure);",
3413    pg("monitoring-stats.html#MONITORING-STATS-FUNCTIONS")
3414  );
3415  f!(
3416    "pg_is_other_temp_schema",
3417    "pg_is_other_temp_schema(oid) -> boolean",
3418    "True when the schema is a temp schema belonging to another session.",
3419    "SELECT pg_is_other_temp_schema(nspid) FROM pg_namespace;",
3420    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3421  );
3422
3423  // ---- cycle2 round 9: pg_get_* introspection fns ----
3424  f!(
3425    "pg_get_viewdef",
3426    "pg_get_viewdef(view_oid|view_name regclass [, pretty boolean | wrap_column int]) -> text",
3427    "Return the underlying SELECT for a view.",
3428    "SELECT pg_get_viewdef('public.v_user_summary', true);",
3429    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3430  );
3431  f!(
3432    "pg_get_indexdef",
3433    "pg_get_indexdef(index_oid regclass [, column int, pretty boolean]) -> text",
3434    "Reconstruct `CREATE INDEX` DDL.",
3435    "SELECT pg_get_indexdef('users_email_idx'::regclass);",
3436    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3437  );
3438  f!(
3439    "pg_get_constraintdef",
3440    "pg_get_constraintdef(constraint_oid [, pretty boolean]) -> text",
3441    "Reconstruct the constraint definition (USING / CHECK / FK clauses).",
3442    "SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'users'::regclass;",
3443    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3444  );
3445  f!(
3446    "pg_get_functiondef",
3447    "pg_get_functiondef(function_oid regprocedure) -> text",
3448    "Reconstruct full `CREATE OR REPLACE FUNCTION` DDL.",
3449    "SELECT pg_get_functiondef('my_fn(int)'::regprocedure);",
3450    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3451  );
3452  f!(
3453    "pg_get_triggerdef",
3454    "pg_get_triggerdef(trigger_oid [, pretty boolean]) -> text",
3455    "Reconstruct `CREATE TRIGGER` DDL.",
3456    "SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE NOT tgisinternal;",
3457    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3458  );
3459  f!(
3460    "pg_get_userbyid",
3461    "pg_get_userbyid(role_oid) -> name",
3462    "Resolve a role OID to its name. Useful when joining catalog tables exposing OIDs.",
3463    "SELECT pg_get_userbyid(relowner) FROM pg_class WHERE relname = 'users';",
3464    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3465  );
3466  f!(
3467    "pg_get_serial_sequence",
3468    "pg_get_serial_sequence(table_name text, column_name text) -> text",
3469    "Sequence backing a `serial`/`identity` column, fully qualified.",
3470    "SELECT pg_get_serial_sequence('users', 'id');",
3471    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3472  );
3473  f!(
3474    "pg_get_expr",
3475    "pg_get_expr(node_tree pg_node_tree, relation regclass [, pretty boolean]) -> text",
3476    "Render a stored expression tree (e.g. column default, partition bound).",
3477    "SELECT attname, pg_get_expr(adbin, adrelid) FROM pg_attrdef JOIN pg_attribute USING (adrelid, adnum);",
3478    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3479  );
3480  f!(
3481    "pg_get_statisticsobjdef",
3482    "pg_get_statisticsobjdef(statistic_oid) -> text",
3483    "Reconstruct `CREATE STATISTICS` DDL.",
3484    "SELECT pg_get_statisticsobjdef(oid) FROM pg_statistic_ext;",
3485    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3486  );
3487  f!(
3488    "pg_relation_filepath",
3489    "pg_relation_filepath(regclass) -> text",
3490    "Filesystem path (relative to data dir) of a relation's main fork.",
3491    "SELECT pg_relation_filepath('users');",
3492    pg("functions-admin.html#FUNCTIONS-ADMIN-DBLOCATION")
3493  );
3494  f!(
3495    "pg_filenode_relation",
3496    "pg_filenode_relation(tablespace_oid oid, filenode oid) -> regclass",
3497    "Inverse of `pg_relation_filenode`.",
3498    "SELECT pg_filenode_relation(1663, 16384);",
3499    pg("functions-admin.html#FUNCTIONS-ADMIN-DBLOCATION")
3500  );
3501  f!(
3502    "pg_log_backend_memory_contexts",
3503    "pg_log_backend_memory_contexts(pid int) -> boolean",
3504    "Write target backend's memory contexts to the server log (PG14+).",
3505    "SELECT pg_log_backend_memory_contexts(12345);",
3506    pg("functions-admin.html#FUNCTIONS-ADMIN-SERVER-SIGNALING")
3507  );
3508  f!(
3509    "pg_typeof",
3510    "pg_typeof(\"any\") -> regtype",
3511    "Return the type of the expression as a regtype.",
3512    "SELECT pg_typeof(NULL::int);",
3513    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3514  );
3515  f!(
3516    "pg_column_compression",
3517    "pg_column_compression(\"any\") -> text",
3518    "Compression method used on a TOASTed value ('pglz', 'lz4', etc).",
3519    "SELECT pg_column_compression(body) FROM articles LIMIT 1;",
3520    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3521  );
3522  f!(
3523    "pg_tablespace_databases",
3524    "pg_tablespace_databases(oid) -> setof oid",
3525    "Databases that have at least one object in the tablespace.",
3526    "SELECT * FROM pg_tablespace_databases(1663);",
3527    pg("functions-admin.html")
3528  );
3529  f!(
3530    "pg_tablespace_location",
3531    "pg_tablespace_location(oid) -> text",
3532    "Filesystem path of a tablespace (empty for pg_default / pg_global).",
3533    "SELECT pg_tablespace_location(oid) FROM pg_tablespace;",
3534    pg("functions-admin.html")
3535  );
3536  f!(
3537    "pg_options_to_table",
3538    "pg_options_to_table(options_array text[]) -> setof (option_name text, option_value text)",
3539    "Expand a `name=value` array (as stored in pg_foreign_server etc) to rows.",
3540    "SELECT * FROM pg_options_to_table(srvoptions) FROM pg_foreign_server;",
3541    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3542  );
3543
3544  // ---- cycle2 round 9: monitoring + encoding + visibility fns ----
3545  f!(
3546    "pg_safe_snapshot_blocking_pids",
3547    "pg_safe_snapshot_blocking_pids(pid int) -> int[]",
3548    "Pids holding the SERIALIZABLE snapshot a given pid is waiting for.",
3549    "SELECT pg_safe_snapshot_blocking_pids(pg_backend_pid());",
3550    pg("functions-info.html#FUNCTIONS-INFO-SESSION")
3551  );
3552  f!(
3553    "pg_isolation_test_session_is_blocked",
3554    "pg_isolation_test_session_is_blocked(pid int, interesting_pids int[]) -> boolean",
3555    "Internal helper used by `isolationtester`. Mostly for PG dev/test.",
3556    "SELECT pg_isolation_test_session_is_blocked(pid, ARRAY[other_pid]);",
3557    pg("functions-info.html")
3558  );
3559  f!(
3560    "pg_collation_actual_version",
3561    "pg_collation_actual_version(collation_oid) -> text",
3562    "OS-reported version of an ICU/libc collation -- compare with `pg_collation.collversion` to detect upgrades that break sort order.",
3563    "SELECT collname, pg_collation_actual_version(oid) FROM pg_collation WHERE collprovider IN ('i','d');",
3564    pg("collation.html")
3565  );
3566  f!(
3567    "pg_collation_for",
3568    "pg_collation_for(\"any\") -> text",
3569    "Collation of an expression as a quoted identifier.",
3570    "SELECT pg_collation_for('hello'::text);",
3571    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3572  );
3573  f!(
3574    "pg_encoding_to_char",
3575    "pg_encoding_to_char(encoding int) -> name",
3576    "Encoding id (pg_database.encoding) to its name.",
3577    "SELECT pg_encoding_to_char(encoding) FROM pg_database;",
3578    pg("functions-info.html")
3579  );
3580  f!(
3581    "pg_char_to_encoding",
3582    "pg_char_to_encoding(text) -> int",
3583    "Encoding name to id.",
3584    "SELECT pg_char_to_encoding('UTF8');",
3585    pg("functions-info.html")
3586  );
3587  f!(
3588    "pg_client_encoding",
3589    "pg_client_encoding() -> name",
3590    "Client encoding for the current session.",
3591    "SELECT pg_client_encoding();",
3592    pg("functions-info.html")
3593  );
3594  f!(
3595    "format_type",
3596    "format_type(type_oid oid, typmod int) -> text",
3597    "Pretty-print a type with its typmod (e.g. `numeric(12,2)`).",
3598    "SELECT format_type(atttypid, atttypmod) FROM pg_attribute WHERE attrelid = 'users'::regclass;",
3599    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3600  );
3601  f!(
3602    "pg_type_is_visible",
3603    "pg_type_is_visible(type_oid) -> boolean",
3604    "True when the type is reachable without schema-qualification under the current search_path.",
3605    "SELECT pg_type_is_visible(oid) FROM pg_type WHERE typname = 'myenum';",
3606    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3607  );
3608  f!(
3609    "pg_table_is_visible",
3610    "pg_table_is_visible(table_oid) -> boolean",
3611    "True when the table/view/sequence is reachable without schema-qualification.",
3612    "SELECT pg_table_is_visible('users'::regclass);",
3613    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3614  );
3615  f!(
3616    "pg_function_is_visible",
3617    "pg_function_is_visible(function_oid) -> boolean",
3618    "True when the function is reachable without schema-qualification.",
3619    "SELECT pg_function_is_visible('now()'::regprocedure);",
3620    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3621  );
3622  f!(
3623    "pg_event_trigger_ddl_commands",
3624    "pg_event_trigger_ddl_commands() -> setof record",
3625    "From inside an event trigger: rows describing the DDL command(s) running.",
3626    "-- inside event trigger body\nFOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP ... END LOOP;",
3627    pg("functions-event-triggers.html")
3628  );
3629  f!(
3630    "pg_event_trigger_dropped_objects",
3631    "pg_event_trigger_dropped_objects() -> setof record",
3632    "From inside an `sql_drop` event trigger: rows for each dropped object.",
3633    "FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP ... END LOOP;",
3634    pg("functions-event-triggers.html")
3635  );
3636  f!(
3637    "pg_event_trigger_table_rewrite_oid",
3638    "pg_event_trigger_table_rewrite_oid() -> oid",
3639    "Inside a `table_rewrite` event trigger: OID of the table being rewritten.",
3640    "SELECT pg_event_trigger_table_rewrite_oid();",
3641    pg("functions-event-triggers.html")
3642  );
3643  f!(
3644    "pg_event_trigger_table_rewrite_reason",
3645    "pg_event_trigger_table_rewrite_reason() -> int",
3646    "Bitmask describing why the table is being rewritten.",
3647    "SELECT pg_event_trigger_table_rewrite_reason();",
3648    pg("functions-event-triggers.html")
3649  );
3650
3651  // ---- cycle2 round 9: SQL/JSON + soft-input + FTS internals ----
3652  f!(
3653    "json_table",
3654    "JSON_TABLE(<json> '<jsonpath>' [PASSING ...] COLUMNS (...)) -- shred JSON to a relational result set (SQL:2023 / PG17+).",
3655    "Like XMLTABLE for JSON.",
3656    "SELECT * FROM JSON_TABLE(j, '$.items[*]' COLUMNS (id INT PATH '$.id', name TEXT PATH '$.name'));",
3657    pg("functions-json.html#FUNCTIONS-SQLJSON-TABLE")
3658  );
3659  f!(
3660    "jsonb_to_tsvector",
3661    "jsonb_to_tsvector([config,] jsonb, filter jsonb) -> tsvector",
3662    "Build a tsvector from JSON, filtered by element-type list ('[\"string\",\"numeric\"]' etc).",
3663    "SELECT jsonb_to_tsvector('english', j, '[\"string\"]');",
3664    pg("functions-textsearch.html#TEXTSEARCH-FUNCTIONS-TABLE")
3665  );
3666  f!(
3667    "json_to_tsvector",
3668    "json_to_tsvector([config,] json, filter jsonb) -> tsvector",
3669    "Same as `jsonb_to_tsvector` for `json`.",
3670    "SELECT json_to_tsvector('english', j, '[\"all\"]');",
3671    pg("functions-textsearch.html#TEXTSEARCH-FUNCTIONS-TABLE")
3672  );
3673  f!(
3674    "json_query",
3675    "JSON_QUERY(<json>, '<jsonpath>' [RETURNING <type>] [WRAPPER ...] [{ERROR|NULL|EMPTY ARRAY|EMPTY OBJECT|DEFAULT <e>} ON EMPTY|ON ERROR])",
3676    "SQL:2023 JSON_QUERY (PG17+) -- return JSON value(s) matching jsonpath; configurable behavior on empty/error.",
3677    "SELECT JSON_QUERY(j, '$.tags' RETURNING jsonb NULL ON EMPTY);",
3678    pg("functions-json.html#FUNCTIONS-SQLJSON-QUERY")
3679  );
3680  f!(
3681    "json_value",
3682    "JSON_VALUE(<json>, '<jsonpath>' [RETURNING <type>] [{ERROR|NULL|DEFAULT <e>} ON EMPTY|ON ERROR])",
3683    "SQL:2023 JSON_VALUE (PG17+) -- extract a single SQL scalar value.",
3684    "SELECT JSON_VALUE(j, '$.user.id' RETURNING bigint DEFAULT 0 ON ERROR);",
3685    pg("functions-json.html#FUNCTIONS-SQLJSON-QUERY")
3686  );
3687  f!(
3688    "json_exists",
3689    "JSON_EXISTS(<json>, '<jsonpath>' [{TRUE|FALSE|UNKNOWN|ERROR} ON ERROR])",
3690    "SQL:2023 JSON_EXISTS (PG17+) -- boolean does-it-match.",
3691    "SELECT JSON_EXISTS(j, '$.user.email');",
3692    pg("functions-json.html#FUNCTIONS-SQLJSON-QUERY")
3693  );
3694  f!(
3695    "pg_input_is_valid",
3696    "pg_input_is_valid(input text, type text) -> boolean",
3697    "PG16+: would casting `input` to `type` succeed (without throwing)?",
3698    "SELECT pg_input_is_valid('42x', 'integer');",
3699    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3700  );
3701  f!(
3702    "pg_input_error_info",
3703    "pg_input_error_info(input text, type text) -> setof (message text, detail text, hint text, sql_error_code text)",
3704    "PG16+: detail rows for why a cast would fail; empty when valid.",
3705    "SELECT * FROM pg_input_error_info('42x', 'integer');",
3706    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
3707  );
3708  f!(
3709    "ts_lexize",
3710    "ts_lexize(dictionary regdictionary, token text) -> text[]",
3711    "Run a single token through an FTS dictionary; useful for debugging.",
3712    "SELECT ts_lexize('english_stem', 'running');",
3713    pg("functions-textsearch.html#TEXTSEARCH-FUNCTIONS-TABLE")
3714  );
3715  f!(
3716    "ts_token_type",
3717    "ts_token_type([parser regconfig|name]) -> setof (tokid int, alias text, description text)",
3718    "Token types a parser can emit.",
3719    "SELECT * FROM ts_token_type('default');",
3720    pg("functions-textsearch.html#TEXTSEARCH-FUNCTIONS-TABLE")
3721  );
3722  f!(
3723    "ts_parse",
3724    "ts_parse(parser_name|oid, text) -> setof (tokid int, token text)",
3725    "Run a parser over text; useful for FTS debugging.",
3726    "SELECT * FROM ts_parse('default', 'The quick brown fox');",
3727    pg("functions-textsearch.html#TEXTSEARCH-FUNCTIONS-TABLE")
3728  );
3729  f!(
3730    "ts_debug",
3731    "ts_debug([config,] text) -> setof record",
3732    "Shows each token, its dictionaries, and lexemes -- the FTS debugging Swiss knife.",
3733    "SELECT * FROM ts_debug('english', 'The quick brown fox');",
3734    pg("textsearch-debugging.html")
3735  );
3736
3737  // ---- cycle2 round 10: replication slot / origin / WAL fns ----
3738  f!(
3739    "pg_replication_origin_create",
3740    "pg_replication_origin_create(node_name text) -> oid",
3741    "Create a replication origin identifier (logical replication / pglogical-style apply tracking).",
3742    "SELECT pg_replication_origin_create('upstream_1');",
3743    pg("functions-admin.html#FUNCTIONS-REPLICATION")
3744  );
3745  f!(
3746    "pg_replication_origin_drop",
3747    "pg_replication_origin_drop(node_name text) -> void",
3748    "Delete a replication origin.",
3749    "SELECT pg_replication_origin_drop('upstream_1');",
3750    pg("functions-admin.html#FUNCTIONS-REPLICATION")
3751  );
3752  f!(
3753    "pg_replication_origin_session_setup",
3754    "pg_replication_origin_session_setup(node_name text) -> void",
3755    "Mark current session as applying changes from `node_name`. Pair with `_session_reset` at exit.",
3756    "SELECT pg_replication_origin_session_setup('upstream_1');",
3757    pg("functions-admin.html#FUNCTIONS-REPLICATION")
3758  );
3759  f!(
3760    "pg_replication_origin_session_reset",
3761    "pg_replication_origin_session_reset() -> void",
3762    "Clear the origin association on the current session.",
3763    "SELECT pg_replication_origin_session_reset();",
3764    pg("functions-admin.html#FUNCTIONS-REPLICATION")
3765  );
3766  f!(
3767    "pg_logical_slot_peek_changes",
3768    "pg_logical_slot_peek_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges int, VARIADIC options text[]) -> setof (lsn pg_lsn, xid xid, data text)",
3769    "Like `pg_logical_slot_get_changes` but does NOT advance the slot -- repeatable reads.",
3770    "SELECT * FROM pg_logical_slot_peek_changes('my_slot', NULL, NULL);",
3771    pg("functions-admin.html#FUNCTIONS-REPLICATION")
3772  );
3773  f!(
3774    "pg_logical_slot_get_binary_changes",
3775    "pg_logical_slot_get_binary_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges int, VARIADIC options text[]) -> setof (lsn pg_lsn, xid xid, data bytea)",
3776    "Drain a binary-output logical replication slot; consumes changes.",
3777    "SELECT * FROM pg_logical_slot_get_binary_changes('my_slot', NULL, NULL);",
3778    pg("functions-admin.html#FUNCTIONS-REPLICATION")
3779  );
3780  f!(
3781    "pg_logical_slot_peek_binary_changes",
3782    "pg_logical_slot_peek_binary_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges int, VARIADIC options text[]) -> setof (lsn pg_lsn, xid xid, data bytea)",
3783    "Peek at binary changes without advancing the slot.",
3784    "SELECT * FROM pg_logical_slot_peek_binary_changes('my_slot', NULL, NULL);",
3785    pg("functions-admin.html#FUNCTIONS-REPLICATION")
3786  );
3787  f!(
3788    "pg_replication_origin_oid",
3789    "pg_replication_origin_oid(node_name text) -> oid",
3790    "OID of a named replication origin.",
3791    "SELECT pg_replication_origin_oid('upstream_1');",
3792    pg("functions-admin.html#FUNCTIONS-REPLICATION")
3793  );
3794  f!(
3795    "pg_replication_origin_session_progress",
3796    "pg_replication_origin_session_progress(flush boolean) -> pg_lsn",
3797    "Last applied LSN tracked for the session's origin.",
3798    "SELECT pg_replication_origin_session_progress(true);",
3799    pg("functions-admin.html#FUNCTIONS-REPLICATION")
3800  );
3801  f!(
3802    "pg_show_replication_origin_status",
3803    "pg_show_replication_origin_status() -> setof (local_id oid, external_id text, remote_lsn pg_lsn, local_lsn pg_lsn)",
3804    "Snapshot of every replication origin's progress.",
3805    "SELECT * FROM pg_show_replication_origin_status();",
3806    pg("functions-admin.html#FUNCTIONS-REPLICATION")
3807  );
3808  f!(
3809    "pg_get_wal_resource_managers",
3810    "pg_get_wal_resource_managers() -> setof (rm_id int, rm_name text, rm_builtin boolean)",
3811    "PG15+: WAL resource managers known to the server (Heap, Btree, custom RMGRs).",
3812    "SELECT * FROM pg_get_wal_resource_managers();",
3813    pg("functions-admin.html#FUNCTIONS-CONTROLDATA")
3814  );
3815  f!(
3816    "pg_get_wal_replay_pause_state",
3817    "pg_get_wal_replay_pause_state() -> text",
3818    "Standby-only: 'not paused' / 'pause requested' / 'paused'.",
3819    "SELECT pg_get_wal_replay_pause_state();",
3820    pg("functions-admin.html#FUNCTIONS-RECOVERY-CONTROL")
3821  );
3822  f!(
3823    "pg_is_wal_replay_paused",
3824    "pg_is_wal_replay_paused() -> boolean",
3825    "Standby-only: WAL replay currently paused?",
3826    "SELECT pg_is_wal_replay_paused();",
3827    pg("functions-admin.html#FUNCTIONS-RECOVERY-CONTROL")
3828  );
3829  f!(
3830    "pg_wal_replay_pause",
3831    "pg_wal_replay_pause() -> void",
3832    "Standby-only: pause WAL replay.",
3833    "SELECT pg_wal_replay_pause();",
3834    pg("functions-admin.html#FUNCTIONS-RECOVERY-CONTROL")
3835  );
3836  f!(
3837    "pg_wal_replay_resume",
3838    "pg_wal_replay_resume() -> void",
3839    "Standby-only: resume WAL replay.",
3840    "SELECT pg_wal_replay_resume();",
3841    pg("functions-admin.html#FUNCTIONS-RECOVERY-CONTROL")
3842  );
3843
3844  // ---- cycle2 round 10: listen/notify queue + lo_* (large object) ----
3845  f!(
3846    "pg_notification_queue_usage",
3847    "pg_notification_queue_usage() -> double precision",
3848    "Fraction (0..1) of the global async-notify queue currently used. >0.5 = pageable.",
3849    "SELECT pg_notification_queue_usage();",
3850    pg("functions-info.html#FUNCTIONS-INFO-SESSION")
3851  );
3852  f!(
3853    "lo_create",
3854    "lo_create(loid oid) -> oid",
3855    "Create a large object with the given OID (0 = pick free OID).",
3856    "SELECT lo_create(0);",
3857    pg("lo-funcs.html")
3858  );
3859  f!(
3860    "lo_unlink",
3861    "lo_unlink(loid oid) -> int",
3862    "Delete a large object.",
3863    "SELECT lo_unlink(16385);",
3864    pg("lo-funcs.html")
3865  );
3866  f!(
3867    "lo_open",
3868    "lo_open(loid oid, mode int) -> int",
3869    "Open a large object; modes: INV_READ=0x40000, INV_WRITE=0x20000.",
3870    "SELECT lo_open(16385, 0x40000);",
3871    pg("lo-funcs.html")
3872  );
3873  f!(
3874    "lo_close",
3875    "lo_close(fd int) -> int",
3876    "Close a large-object file descriptor.",
3877    "SELECT lo_close(fd);",
3878    pg("lo-funcs.html")
3879  );
3880  f!(
3881    "lo_read",
3882    "lo_read(fd int, len int) -> bytea",
3883    "Read up to len bytes from a large-object fd.",
3884    "SELECT lo_read(fd, 8192);",
3885    pg("lo-funcs.html")
3886  );
3887  f!(
3888    "lo_write",
3889    "lo_write(fd int, data bytea) -> int",
3890    "Write bytes to a large-object fd.",
3891    "SELECT lo_write(fd, data);",
3892    pg("lo-funcs.html")
3893  );
3894  f!(
3895    "lo_lseek",
3896    "lo_lseek(fd int, offset int, whence int) -> int",
3897    "32-bit seek inside a large object. Whence: 0=SET, 1=CUR, 2=END.",
3898    "SELECT lo_lseek(fd, 0, 0);",
3899    pg("lo-funcs.html")
3900  );
3901  f!(
3902    "lo_lseek64",
3903    "lo_lseek64(fd int, offset bigint, whence int) -> bigint",
3904    "64-bit seek for large objects > 2 GB.",
3905    "SELECT lo_lseek64(fd, 3000000000, 0);",
3906    pg("lo-funcs.html")
3907  );
3908  f!("lo_tell", "lo_tell(fd int) -> int", "Current offset (32-bit).", "SELECT lo_tell(fd);", pg("lo-funcs.html"));
3909  f!(
3910    "lo_tell64",
3911    "lo_tell64(fd int) -> bigint",
3912    "Current offset (64-bit).",
3913    "SELECT lo_tell64(fd);",
3914    pg("lo-funcs.html")
3915  );
3916  f!(
3917    "lo_truncate",
3918    "lo_truncate(fd int, len int) -> int",
3919    "Truncate (32-bit).",
3920    "SELECT lo_truncate(fd, 0);",
3921    pg("lo-funcs.html")
3922  );
3923  f!(
3924    "lo_truncate64",
3925    "lo_truncate64(fd int, len bigint) -> int",
3926    "Truncate (64-bit).",
3927    "SELECT lo_truncate64(fd, 0);",
3928    pg("lo-funcs.html")
3929  );
3930  f!(
3931    "lo_put",
3932    "lo_put(loid oid, offset bigint, data bytea) -> void",
3933    "Overwrite bytes at offset -- whole-object form, no fd dance.",
3934    "SELECT lo_put(16385, 0, '\\xdeadbeef');",
3935    pg("lo-funcs.html")
3936  );
3937  f!(
3938    "lo_get",
3939    "lo_get(loid oid [, offset bigint, length int]) -> bytea",
3940    "Read part (or all) of a large object as bytea.",
3941    "SELECT lo_get(16385, 0, 1024);",
3942    pg("lo-funcs.html")
3943  );
3944  f!(
3945    "lo_from_bytea",
3946    "lo_from_bytea(loid oid, data bytea) -> oid",
3947    "Create a large object from bytea.",
3948    "SELECT lo_from_bytea(0, '\\xdeadbeef');",
3949    pg("lo-funcs.html")
3950  );
3951  f!(
3952    "lo_export",
3953    "lo_export(loid oid, file text) -> int",
3954    "Write a large object to a server-side file (superuser).",
3955    "SELECT lo_export(16385, '/tmp/blob.bin');",
3956    pg("lo-funcs.html")
3957  );
3958  f!(
3959    "lo_import",
3960    "lo_import(file text [, loid oid]) -> oid",
3961    "Load a server-side file into a large object (superuser).",
3962    "SELECT lo_import('/tmp/blob.bin');",
3963    pg("lo-funcs.html")
3964  );
3965
3966  // ---- cycle2 round 10: server-side fs fns ----
3967  f!(
3968    "pg_ls_dir",
3969    "pg_ls_dir(dir text [, missing_ok boolean, include_dot_dirs boolean]) -> setof text",
3970    "List entries in a server-side directory (superuser or pg_read_server_files).",
3971    "SELECT pg_ls_dir('base');",
3972    pg("functions-admin.html#FUNCTIONS-ADMIN-GENFILE")
3973  );
3974  f!(
3975    "pg_ls_logdir",
3976    "pg_ls_logdir() -> setof (name text, size bigint, modification timestamptz)",
3977    "List the server log directory contents.",
3978    "SELECT * FROM pg_ls_logdir();",
3979    pg("functions-admin.html#FUNCTIONS-ADMIN-GENFILE")
3980  );
3981  f!(
3982    "pg_ls_waldir",
3983    "pg_ls_waldir() -> setof (name text, size bigint, modification timestamptz)",
3984    "List pg_wal/.",
3985    "SELECT * FROM pg_ls_waldir();",
3986    pg("functions-admin.html#FUNCTIONS-ADMIN-GENFILE")
3987  );
3988  f!(
3989    "pg_ls_tmpdir",
3990    "pg_ls_tmpdir([tablespace oid]) -> setof (name text, size bigint, modification timestamptz)",
3991    "List the temp directory of a tablespace.",
3992    "SELECT * FROM pg_ls_tmpdir();",
3993    pg("functions-admin.html#FUNCTIONS-ADMIN-GENFILE")
3994  );
3995  f!(
3996    "pg_ls_archive_statusdir",
3997    "pg_ls_archive_statusdir() -> setof (name text, size bigint, modification timestamptz)",
3998    "List pg_wal/archive_status/.",
3999    "SELECT * FROM pg_ls_archive_statusdir();",
4000    pg("functions-admin.html#FUNCTIONS-ADMIN-GENFILE")
4001  );
4002  f!(
4003    "pg_read_file",
4004    "pg_read_file(filename text [, offset bigint, length bigint [, missing_ok boolean]]) -> text",
4005    "Read a server-side file as text (superuser or pg_read_server_files).",
4006    "SELECT pg_read_file('postgresql.conf', 0, 4096);",
4007    pg("functions-admin.html#FUNCTIONS-ADMIN-GENFILE")
4008  );
4009  f!(
4010    "pg_read_binary_file",
4011    "pg_read_binary_file(filename text [, offset bigint, length bigint [, missing_ok boolean]]) -> bytea",
4012    "Binary counterpart of `pg_read_file`.",
4013    "SELECT length(pg_read_binary_file('PG_VERSION'));",
4014    pg("functions-admin.html#FUNCTIONS-ADMIN-GENFILE")
4015  );
4016  f!(
4017    "pg_stat_file",
4018    "pg_stat_file(filename text [, missing_ok boolean]) -> record",
4019    "Server-side `stat()` info: size, modification, change, access, creation, isdir.",
4020    "SELECT * FROM pg_stat_file('postgresql.conf');",
4021    pg("functions-admin.html#FUNCTIONS-ADMIN-GENFILE")
4022  );
4023
4024  // ---- cycle2 round 10: dblink (contrib) ----
4025  f!(
4026    "dblink",
4027    "dblink(connstr text, sql text) -> setof record",
4028    "Execute a query on a remote PG via dblink and return rows. Caller supplies the AS (col types) list.",
4029    "SELECT * FROM dblink('host=remote dbname=app', 'SELECT id, name FROM t') AS r(id int, name text);",
4030    pg("dblink.html")
4031  );
4032  f!(
4033    "dblink_exec",
4034    "dblink_exec(connstr text, sql text) -> text",
4035    "Execute a non-returning SQL on a remote server.",
4036    "SELECT dblink_exec('myconn', 'INSERT INTO t VALUES (1)');",
4037    pg("dblink.html")
4038  );
4039  f!(
4040    "dblink_connect",
4041    "dblink_connect(name text, connstr text) -> text",
4042    "Open a named persistent dblink connection.",
4043    "SELECT dblink_connect('myconn', 'host=remote dbname=app');",
4044    pg("dblink.html")
4045  );
4046  f!(
4047    "dblink_disconnect",
4048    "dblink_disconnect(name text) -> text",
4049    "Close a named dblink connection.",
4050    "SELECT dblink_disconnect('myconn');",
4051    pg("dblink.html")
4052  );
4053  f!(
4054    "dblink_get_connections",
4055    "dblink_get_connections() -> text[]",
4056    "Names of all open dblink connections in this session.",
4057    "SELECT dblink_get_connections();",
4058    pg("dblink.html")
4059  );
4060
4061  // ---- cycle2 round 10: misc helpers ----
4062  f!(
4063    "num_nonnulls",
4064    "num_nonnulls(VARIADIC \"any\") -> int",
4065    "Count non-NULL args (PG10+).",
4066    "SELECT num_nonnulls(a, b, c) FROM t;",
4067    pg("functions-comparison.html#FUNCTIONS-COMPARISON-FUNC-TABLE")
4068  );
4069  f!(
4070    "num_nulls",
4071    "num_nulls(VARIADIC \"any\") -> int",
4072    "Count NULL args.",
4073    "SELECT num_nulls(a, b, c) FROM t;",
4074    pg("functions-comparison.html#FUNCTIONS-COMPARISON-FUNC-TABLE")
4075  );
4076  f!(
4077    "suppress_redundant_updates_trigger",
4078    "suppress_redundant_updates_trigger() -> trigger",
4079    "BEFORE UPDATE trigger that skips the row update when nothing changed -- cheaper than recomputing in app code.",
4080    "CREATE TRIGGER skip_noop BEFORE UPDATE ON t FOR EACH ROW EXECUTE FUNCTION suppress_redundant_updates_trigger();",
4081    pg("functions-trigger.html")
4082  );
4083  f!(
4084    "tsmatchsel",
4085    "tsmatchsel(internal, oid, internal, int) -> double precision",
4086    "Selectivity-estimator for the `@@` FTS match operator. Internal -- referenced by `CREATE OPERATOR ... RESTRICT = tsmatchsel`.",
4087    "-- internal",
4088    pg("internals/index-functions.html")
4089  );
4090  f!(
4091    "tsmatchjoinsel",
4092    "tsmatchjoinsel(internal, oid, internal, smallint, internal) -> double precision",
4093    "Join-selectivity estimator counterpart of `tsmatchsel`.",
4094    "-- internal",
4095    pg("internals/index-functions.html")
4096  );
4097
4098  // ---- cycle2 round 69: misc + GUC + encoding helpers ----
4099  f!(
4100    "width_bucket",
4101    "width_bucket(value, low, high, count int) -> int | width_bucket(value, array) -> int",
4102    "Map value into one of N equiwidth buckets between low/high, or into a sorted-array bucket. Useful for histograms.",
4103    "SELECT width_bucket(score, 0, 100, 10) AS bucket, count(*) FROM scores GROUP BY 1 ORDER BY 1;",
4104    pg("functions-math.html")
4105  );
4106  f!(
4107    "bound_box",
4108    "bound_box(box, box) -> box",
4109    "Smallest box containing both input boxes.",
4110    "SELECT bound_box(box '(0,0),(1,1)', box '(2,2),(3,3)');",
4111    pg("functions-geometry.html")
4112  );
4113  f!(
4114    "convert",
4115    "convert(bytea, src_encoding text, dst_encoding text) -> bytea",
4116    "Convert bytes between encodings. Companion of `convert_from` / `convert_to`.",
4117    "SELECT convert('\\xc3a9'::bytea, 'UTF8', 'LATIN1');",
4118    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
4119  );
4120  f!(
4121    "convert_from",
4122    "convert_from(bytea, src_encoding text) -> text",
4123    "Decode bytes from a given encoding into a text value (using the server encoding).",
4124    "SELECT convert_from('\\xc3a9'::bytea, 'UTF8');",
4125    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
4126  );
4127  f!(
4128    "convert_to",
4129    "convert_to(text, dst_encoding text) -> bytea",
4130    "Encode text into a target encoding's byte representation.",
4131    "SELECT convert_to('café', 'LATIN1');",
4132    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
4133  );
4134  f!(
4135    "set_config",
4136    "set_config(setting_name text, new_value text, is_local boolean) -> text",
4137    "Equivalent of `SET`/`SET LOCAL`. Returns the new value as text.",
4138    "SELECT set_config('statement_timeout', '5s', false);",
4139    pg("functions-admin.html#FUNCTIONS-ADMIN-SET")
4140  );
4141  f!(
4142    "current_setting",
4143    "current_setting(setting_name text [, missing_ok boolean]) -> text",
4144    "Equivalent of `SHOW`. Returns the current GUC value as text. `missing_ok` true returns NULL when unknown.",
4145    "SELECT current_setting('search_path');",
4146    pg("functions-admin.html#FUNCTIONS-ADMIN-SET")
4147  );
4148  f!(
4149    "cluster_name",
4150    "cluster_name -> text",
4151    "Server-wide cluster_name GUC (set in postgresql.conf or via ALTER SYSTEM).",
4152    "SELECT cluster_name;",
4153    pg("functions-info.html")
4154  );
4155  f!(
4156    "pg_jit_available",
4157    "pg_jit_available() -> boolean",
4158    "True when the server has a JIT provider compiled in and `jit` GUC enabled.",
4159    "SELECT pg_jit_available();",
4160    pg("functions-info.html")
4161  );
4162  f!(
4163    "pg_index_column_has_property",
4164    "pg_index_column_has_property(index_oid regclass, column_no int, property text) -> boolean",
4165    "Test per-column index capabilities (e.g. 'asc', 'desc', 'orderable', 'nulls_first').",
4166    "SELECT pg_index_column_has_property('users_email_idx'::regclass, 1, 'orderable');",
4167    pg("functions-info.html#FUNCTIONS-INFO-INDEX-COLUMN-PROPS")
4168  );
4169  f!(
4170    "pg_index_has_property",
4171    "pg_index_has_property(index_oid regclass, property text) -> boolean",
4172    "Test whole-index capabilities (e.g. 'returnable', 'distance_orderable').",
4173    "SELECT pg_index_has_property('users_email_idx'::regclass, 'distance_orderable');",
4174    pg("functions-info.html")
4175  );
4176  f!(
4177    "pg_indexam_has_property",
4178    "pg_indexam_has_property(am_oid oid, property text) -> boolean",
4179    "Test access-method-level capabilities (e.g. 'can_order', 'can_unique', 'can_multi_col').",
4180    "SELECT pg_indexam_has_property('btree'::regam, 'can_unique');",
4181    pg("functions-info.html")
4182  );
4183  // ---- cycle2 round 69: more JSON fns ----
4184  f!(
4185    "to_jsonb",
4186    "to_jsonb(any) -> jsonb",
4187    "Convert any value to its JSONB representation.",
4188    "SELECT to_jsonb(ARRAY[1,2,3]);",
4189    pg("functions-json.html#FUNCTIONS-JSON-CREATION-TABLE")
4190  );
4191  f!(
4192    "json_strip_nulls",
4193    "json_strip_nulls(json) -> json",
4194    "Recursively drop keys with NULL values from a JSON object.",
4195    "SELECT json_strip_nulls('{\"a\":1,\"b\":null}'::json);",
4196    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
4197  );
4198  f!(
4199    "json_array_length",
4200    "json_array_length(json) -> int",
4201    "Length of a JSON array.",
4202    "SELECT json_array_length('[1,2,3]'::json);",
4203    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
4204  );
4205  f!(
4206    "json_typeof",
4207    "json_typeof(json) -> text",
4208    "JSON type label: 'object'/'array'/'string'/'number'/'boolean'/'null'.",
4209    "SELECT json_typeof('[1,2,3]'::json);",
4210    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
4211  );
4212  f!(
4213    "json_extract_path",
4214    "json_extract_path(json, variadic text[]) -> json",
4215    "Walk a JSON path (key-by-key) and return the matching JSON.",
4216    "SELECT json_extract_path('{\"a\":{\"b\":1}}'::json, 'a', 'b');",
4217    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
4218  );
4219  f!(
4220    "json_extract_path_text",
4221    "json_extract_path_text(json, variadic text[]) -> text",
4222    "Same as `json_extract_path` but coerces the result to text.",
4223    "SELECT json_extract_path_text('{\"a\":1}'::json, 'a');",
4224    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
4225  );
4226  f!(
4227    "json_each",
4228    "json_each(json) -> setof (key text, value json)",
4229    "Iterate object's (key, value) pairs.",
4230    "SELECT * FROM json_each('{\"a\":1,\"b\":2}'::json);",
4231    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
4232  );
4233  f!(
4234    "json_each_text",
4235    "json_each_text(json) -> setof (key text, value text)",
4236    "Same as `json_each` but values cast to text.",
4237    "SELECT * FROM json_each_text('{\"a\":1}'::json);",
4238    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
4239  );
4240  f!(
4241    "json_object_keys",
4242    "json_object_keys(json) -> setof text",
4243    "Top-level keys of a JSON object.",
4244    "SELECT json_object_keys('{\"a\":1,\"b\":2}'::json);",
4245    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
4246  );
4247  f!(
4248    "json_populate_recordset",
4249    "json_populate_recordset(base anyelement, json) -> setof anyelement",
4250    "Materialise an array of JSON objects as rows of a target composite type.",
4251    "SELECT * FROM json_populate_recordset(null::users, '[{\"id\":1},{\"id\":2}]'::json);",
4252    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
4253  );
4254  // ---- cycle2 round 69: more reg / hash fns ----
4255  f!(
4256    "to_regnamespace",
4257    "to_regnamespace(text) -> regnamespace",
4258    "Look up schema OID by name; NULL when missing.",
4259    "SELECT to_regnamespace('public') IS NOT NULL;",
4260    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
4261  );
4262  f!(
4263    "to_regrole",
4264    "to_regrole(text) -> regrole",
4265    "Look up role OID by name; NULL when missing.",
4266    "SELECT to_regrole('postgres') IS NOT NULL;",
4267    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
4268  );
4269  f!(
4270    "to_regoperator",
4271    "to_regoperator(text) -> regoperator",
4272    "Look up operator OID by signature 'op(lt,rt)'; NULL when missing.",
4273    "SELECT to_regoperator('+(int,int)') IS NOT NULL;",
4274    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
4275  );
4276  f!(
4277    "hashtextextended",
4278    "hashtextextended(text, seed bigint) -> bigint",
4279    "64-bit seeded hash of text. PG13+; used by hash partitioning and parallel ops.",
4280    "SELECT hashtextextended('abc', 0);",
4281    pg("functions-binarystring.html")
4282  );
4283  f!(
4284    "hashbpchar",
4285    "hashbpchar(bpchar) -> int",
4286    "Internal hash of a fixed-length character. Referenced by hash index/AM definitions.",
4287    "-- internal",
4288    pg("functions-binarystring.html")
4289  );
4290
4291  // ---- cycle2 round 70: FTS + pg_trgm helpers ----
4292  f!(
4293    "tsvector_to_array",
4294    "tsvector_to_array(tsvector) -> text[]",
4295    "Project lexemes of a tsvector into a text[] (positions/weights dropped).",
4296    "SELECT tsvector_to_array(to_tsvector('a b c'));",
4297    pg("functions-textsearch.html#TEXTSEARCH-FUNCTIONS-TABLE")
4298  );
4299  f!(
4300    "array_to_tsvector",
4301    "array_to_tsvector(text[]) -> tsvector",
4302    "Build a tsvector from an array of lexemes (no normalization).",
4303    "SELECT array_to_tsvector(ARRAY['a','b','c']);",
4304    pg("functions-textsearch.html#TEXTSEARCH-FUNCTIONS-TABLE")
4305  );
4306  f!(
4307    "ts_strip",
4308    "ts_strip(tsvector) -> tsvector",
4309    "Strip positions and weights from a tsvector.",
4310    "SELECT ts_strip(to_tsvector('a:1A b:2'));",
4311    pg("functions-textsearch.html#TEXTSEARCH-FUNCTIONS-TABLE")
4312  );
4313  f!(
4314    "ts_setweight",
4315    "setweight(tsvector, weight char [, lexemes text[]]) -> tsvector",
4316    "Re-weight an entire tsvector (or only given lexemes) with A/B/C/D.",
4317    "SELECT setweight(to_tsvector('quick fox'), 'A');",
4318    pg("functions-textsearch.html#TEXTSEARCH-FUNCTIONS-TABLE")
4319  );
4320  f!(
4321    "ts_length",
4322    "length(tsvector) -> int",
4323    "Number of distinct lexemes in a tsvector.",
4324    "SELECT length(to_tsvector('a a b'));",
4325    pg("functions-textsearch.html#TEXTSEARCH-FUNCTIONS-TABLE")
4326  );
4327  f!(
4328    "similarity",
4329    "similarity(text, text) -> real",
4330    "pg_trgm: trigram similarity in [0, 1]. 0 = no overlap, 1 = identical.",
4331    "SELECT similarity('postgres', 'postgresql');",
4332    pg("pgtrgm.html")
4333  );
4334  f!(
4335    "word_similarity",
4336    "word_similarity(text, text) -> real",
4337    "pg_trgm: best similarity of left-side phrase within right text.",
4338    "SELECT word_similarity('post', 'postgres rocks');",
4339    pg("pgtrgm.html")
4340  );
4341  f!(
4342    "strict_word_similarity",
4343    "strict_word_similarity(text, text) -> real",
4344    "Stricter `word_similarity` that requires word boundaries on the right side.",
4345    "SELECT strict_word_similarity('post', 'postgres rocks');",
4346    pg("pgtrgm.html")
4347  );
4348  f!(
4349    "show_trgm",
4350    "show_trgm(text) -> text[]",
4351    "pg_trgm: trigrams the text decomposes into.",
4352    "SELECT show_trgm('Postgres');",
4353    pg("pgtrgm.html")
4354  );
4355  f!(
4356    "show_limit",
4357    "show_limit() -> real",
4358    "pg_trgm: current similarity threshold for `%` operator.",
4359    "SELECT show_limit();",
4360    pg("pgtrgm.html")
4361  );
4362  f!(
4363    "set_limit",
4364    "set_limit(real) -> real",
4365    "pg_trgm: set the similarity threshold for `%`.",
4366    "SELECT set_limit(0.4);",
4367    pg("pgtrgm.html")
4368  );
4369
4370  // ---- cycle2 round 70: geometric constructors / helpers ----
4371  f!(
4372    "box",
4373    "box(text|point,point|polygon|circle) -> box",
4374    "Construct or convert to a box.",
4375    "SELECT box(point '(0,0)', point '(1,1)');",
4376    pg("functions-geometry.html")
4377  );
4378  f!(
4379    "circle",
4380    "circle(text|point, radius|box|polygon) -> circle",
4381    "Construct or convert to a circle.",
4382    "SELECT circle(point '(0,0)', 5);",
4383    pg("functions-geometry.html")
4384  );
4385  f!(
4386    "line",
4387    "line(point, point) -> line",
4388    "Construct an infinite line through two points.",
4389    "SELECT line(point '(0,0)', point '(1,1)');",
4390    pg("functions-geometry.html")
4391  );
4392  f!(
4393    "lseg",
4394    "lseg(point, point) -> lseg",
4395    "Construct a line segment between two points.",
4396    "SELECT lseg(point '(0,0)', point '(1,1)');",
4397    pg("functions-geometry.html")
4398  );
4399  f!(
4400    "path",
4401    "path(text|polygon) -> path",
4402    "Convert text/polygon into a path.",
4403    "SELECT path(polygon '((0,0),(1,0),(1,1))');",
4404    pg("functions-geometry.html")
4405  );
4406  f!(
4407    "point",
4408    "point(double precision, double precision) -> point",
4409    "Construct a point. Alias `(x, y)::point`.",
4410    "SELECT point(1.0, 2.0);",
4411    pg("functions-geometry.html")
4412  );
4413  f!(
4414    "polygon",
4415    "polygon(text|box|path|circle) -> polygon",
4416    "Construct or convert to a polygon.",
4417    "SELECT polygon(box '(0,0),(1,1)');",
4418    pg("functions-geometry.html")
4419  );
4420  f!(
4421    "circle_in",
4422    "circle_in(cstring) -> circle",
4423    "Type-input function for `circle`. Called by the parser, rarely by users.",
4424    "-- internal",
4425    pg("functions-geometry.html")
4426  );
4427  f!(
4428    "box_in",
4429    "box_in(cstring) -> box",
4430    "Type-input function for `box`. Internal.",
4431    "-- internal",
4432    pg("functions-geometry.html")
4433  );
4434
4435  // ---- cycle2 round 70: date/time fns ----
4436  f!(
4437    "extract",
4438    "EXTRACT(<field> FROM <timestamp|interval>) -> double precision",
4439    "Pull a sub-field (year, month, day, dow, doy, epoch, ...) from a timestamp/interval.",
4440    "SELECT EXTRACT(EPOCH FROM (now() - birth)) FROM users;",
4441    pg("functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT")
4442  );
4443  f!(
4444    "isfinite",
4445    "isfinite(date|timestamp|interval) -> boolean",
4446    "True when the value is not `-infinity`/`infinity`.",
4447    "SELECT isfinite(now());",
4448    pg("functions-datetime.html")
4449  );
4450  f!(
4451    "timezone",
4452    "timezone(text, timestamp|timestamptz|time|timetz) -> ...",
4453    "Equivalent of `AT TIME ZONE`. Convert between zones.",
4454    "SELECT timezone('UTC', now());",
4455    pg("functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT")
4456  );
4457  f!(
4458    "timeofday",
4459    "timeofday() -> text",
4460    "Current wall-clock time as a human-readable string. Cheap debug helper.",
4461    "SELECT timeofday();",
4462    pg("functions-datetime.html#FUNCTIONS-DATETIME-CURRENT")
4463  );
4464  f!(
4465    "clock_timestamp",
4466    "clock_timestamp() -> timestamptz",
4467    "Real-time clock (changes within a statement). Compare with `now()` (transaction-time).",
4468    "SELECT clock_timestamp();",
4469    pg("functions-datetime.html#FUNCTIONS-DATETIME-CURRENT")
4470  );
4471  f!(
4472    "transaction_timestamp",
4473    "transaction_timestamp() -> timestamptz",
4474    "Alias of `now()` -- transaction start time.",
4475    "SELECT transaction_timestamp();",
4476    pg("functions-datetime.html#FUNCTIONS-DATETIME-CURRENT")
4477  );
4478  f!(
4479    "statement_timestamp",
4480    "statement_timestamp() -> timestamptz",
4481    "Current statement start time (constant within one statement).",
4482    "SELECT statement_timestamp();",
4483    pg("functions-datetime.html#FUNCTIONS-DATETIME-CURRENT")
4484  );
4485
4486  // ---- cycle2 round 71: more interval + introspection ----
4487  f!(
4488    "date_bin",
4489    "date_bin(stride interval, source timestamp, origin timestamp) -> timestamp",
4490    "Snap a timestamp to the nearest stride-sized bin starting at `origin`. PG14+. Useful for time-series bucketing.",
4491    "SELECT date_bin('15 minutes', now(), TIMESTAMP '2000-01-01');",
4492    pg("functions-datetime.html#FUNCTIONS-DATETIME-BIN")
4493  );
4494  f!(
4495    "date_diff",
4496    "date_diff(field text, a timestamp, b timestamp) -> bigint",
4497    "PG17+ helper: signed difference between two timestamps in whole units of `field` ('year','month','day','hour','minute','second',...).",
4498    "SELECT date_diff('day', '2026-01-01'::timestamp, now()::timestamp);",
4499    pg("functions-datetime.html")
4500  );
4501  f!(
4502    "pg_get_function_arguments",
4503    "pg_get_function_arguments(function_oid regprocedure) -> text",
4504    "Re-render a function's argument signature, including modes / defaults.",
4505    "SELECT pg_get_function_arguments('my_fn(int)'::regprocedure);",
4506    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
4507  );
4508  f!(
4509    "pg_get_function_result",
4510    "pg_get_function_result(function_oid regprocedure) -> text",
4511    "Re-render a function's RETURNS clause.",
4512    "SELECT pg_get_function_result('my_fn(int)'::regprocedure);",
4513    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
4514  );
4515
4516  // ---- cycle2 round 72: misc string / array / enum / json ----
4517  f!(
4518    "ord",
4519    "ord(text) -> int",
4520    "Unicode codepoint of the first character. PG17+; for older PGs use `ascii(left(s,1))`.",
4521    "SELECT ord('A');",
4522    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
4523  );
4524  f!(
4525    "to_hex",
4526    "to_hex(int|bigint) -> text",
4527    "Lowercase hex representation (no `0x` prefix).",
4528    "SELECT to_hex(255);",
4529    pg("functions-string.html#FUNCTIONS-STRING-OTHER")
4530  );
4531  f!(
4532    "array_subscript",
4533    "array_subscript_handler -- internal type-specific subscript routing",
4534    "Internal type-support function plugged into custom-type subscript handlers (PG14+ subscriptable types).",
4535    "-- internal",
4536    pg("functions-array.html")
4537  );
4538  f!(
4539    "hash_array",
4540    "hash_array(anyarray) -> int",
4541    "Internal hash of an array. Referenced by hash index/joins.",
4542    "-- internal",
4543    pg("functions-array.html")
4544  );
4545  f!(
4546    "bit_count",
4547    "bit_count(bytea|bit) -> bigint",
4548    "Number of set bits (population count). PG14+.",
4549    "SELECT bit_count('\\xff'::bytea);",
4550    pg("functions-binarystring.html")
4551  );
4552  f!(
4553    "array_sample",
4554    "array_sample(array anyarray, n int) -> anyarray",
4555    "Return n elements sampled without replacement (PG16+).",
4556    "SELECT array_sample(ARRAY[1,2,3,4,5], 2);",
4557    pg("functions-array.html#FUNCTIONS-ARRAY-TABLE")
4558  );
4559  f!(
4560    "array_shuffle",
4561    "array_shuffle(anyarray) -> anyarray",
4562    "Return the array elements in random order (PG16+).",
4563    "SELECT array_shuffle(ARRAY[1,2,3,4,5]);",
4564    pg("functions-array.html#FUNCTIONS-ARRAY-TABLE")
4565  );
4566  f!(
4567    "range_intersect_agg",
4568    "range_intersect_agg(anyrange) -> anyrange",
4569    "Aggregate the intersection of a set of ranges. PG14+.",
4570    "SELECT range_intersect_agg(period) FROM availability WHERE resource_id = 1;",
4571    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
4572  );
4573  f!(
4574    "pg_get_catalog_foreign_keys",
4575    "pg_get_catalog_foreign_keys() -> setof record(fktable, fkcols, pktable, pkcols, is_array, is_opt)",
4576    "Hard-coded FK relationships between pg_catalog tables (PG14+).",
4577    "SELECT * FROM pg_get_catalog_foreign_keys();",
4578    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
4579  );
4580  f!(
4581    "pg_get_partition_constraintdef",
4582    "pg_get_partition_constraintdef(oid) -> text",
4583    "Reconstruct the CHECK constraint implied by a partition's bounds.",
4584    "SELECT pg_get_partition_constraintdef('events_2025'::regclass::oid);",
4585    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
4586  );
4587  f!(
4588    "pg_partition_root",
4589    "pg_partition_root(regclass) -> regclass",
4590    "Return the topmost parent of a partitioned-table tree.",
4591    "SELECT pg_partition_root('events_2025'::regclass);",
4592    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
4593  );
4594  f!(
4595    "pg_partition_ancestors",
4596    "pg_partition_ancestors(regclass) -> setof regclass",
4597    "Each ancestor in the partitioning tree, root last.",
4598    "SELECT * FROM pg_partition_ancestors('events_2025'::regclass);",
4599    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
4600  );
4601  f!(
4602    "pg_partition_tree",
4603    "pg_partition_tree(regclass) -> setof record(relid, parentid, isleaf, level)",
4604    "Walk a partitioned table top-down.",
4605    "SELECT * FROM pg_partition_tree('events'::regclass);",
4606    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
4607  );
4608  // ---- fuzzystrmatch (CREATE EXTENSION fuzzystrmatch) ----
4609  f!(
4610    "levenshtein",
4611    "levenshtein(s1 text, s2 text [, ins_cost int, del_cost int, sub_cost int]) -> int",
4612    "Edit distance with optional per-op costs. Requires `CREATE EXTENSION fuzzystrmatch`.",
4613    "SELECT levenshtein('kitten', 'sitting');",
4614    pg("fuzzystrmatch.html#FUZZYSTRMATCH-LEVENSHTEIN")
4615  );
4616  f!(
4617    "levenshtein_less_equal",
4618    "levenshtein_less_equal(s1, s2, max int) -> int",
4619    "Like levenshtein() but bails out early when distance would exceed `max`. Faster on similar inputs.",
4620    "SELECT levenshtein_less_equal('foo', 'fooz', 2);",
4621    pg("fuzzystrmatch.html#FUZZYSTRMATCH-LEVENSHTEIN")
4622  );
4623  f!(
4624    "metaphone",
4625    "metaphone(text, max_output_length int) -> text",
4626    "Metaphone phonetic key for English words. Extension fuzzystrmatch.",
4627    "SELECT metaphone('Pittsburgh', 6);",
4628    pg("fuzzystrmatch.html#FUZZYSTRMATCH-METAPHONE")
4629  );
4630  f!(
4631    "dmetaphone",
4632    "dmetaphone(text) -> text",
4633    "Double-Metaphone primary code (improved Metaphone). Extension fuzzystrmatch.",
4634    "SELECT dmetaphone('Pittsburgh');",
4635    pg("fuzzystrmatch.html#FUZZYSTRMATCH-DOUBLE-METAPHONE")
4636  );
4637  f!(
4638    "dmetaphone_alt",
4639    "dmetaphone_alt(text) -> text",
4640    "Double-Metaphone alternate code (for ambiguous pronunciation). Extension fuzzystrmatch.",
4641    "SELECT dmetaphone_alt('Pittsburgh');",
4642    pg("fuzzystrmatch.html#FUZZYSTRMATCH-DOUBLE-METAPHONE")
4643  );
4644  f!(
4645    "soundex",
4646    "soundex(text) -> text",
4647    "Soundex code (legacy phonetic). Extension fuzzystrmatch.",
4648    "SELECT soundex('Smith') = soundex('Smyth');",
4649    pg("fuzzystrmatch.html#FUZZYSTRMATCH-SOUNDEX")
4650  );
4651  f!(
4652    "difference",
4653    "difference(a text, b text) -> int",
4654    "Soundex distance: 0..4. Extension fuzzystrmatch.",
4655    "SELECT difference('Smith', 'Smyth');",
4656    pg("fuzzystrmatch.html#FUZZYSTRMATCH-SOUNDEX")
4657  );
4658  // ---- unaccent (CREATE EXTENSION unaccent) ----
4659  f!(
4660    "unaccent",
4661    "unaccent([dict regdictionary,] text) -> text",
4662    "Strip accents using a tsearch dictionary (default `unaccent`). Extension unaccent.",
4663    "SELECT unaccent('café');",
4664    pg("unaccent.html")
4665  );
4666  // ---- earthdistance / cube extension helpers (very common) ----
4667  f!(
4668    "ll_to_earth",
4669    "ll_to_earth(lat double, lon double) -> earth",
4670    "Convert a lat/lon to the cube-based `earth` type. Requires `cube` + `earthdistance`.",
4671    "SELECT ll_to_earth(40.7, -74.0);",
4672    pg("earthdistance.html")
4673  );
4674  f!(
4675    "earth_distance",
4676    "earth_distance(a earth, b earth) -> double precision",
4677    "Great-circle distance in meters. Requires cube + earthdistance.",
4678    "SELECT earth_distance(ll_to_earth(40.7,-74.0), ll_to_earth(34.0,-118.0));",
4679    pg("earthdistance.html")
4680  );
4681  // ---- pgcrypto helpers worth surfacing ----
4682  f!(
4683    "crypt_check",
4684    "crypt(password, hash) -> text",
4685    "Verify by comparing a freshly-crypted password to a stored hash (constant-time-friendly). Pair with gen_salt.",
4686    "SELECT (hash = crypt('plaintext', hash)) AS ok FROM users WHERE id = 1;",
4687    pg("pgcrypto.html#PGCRYPTO-PASSWORD-HASHING")
4688  );
4689  // ---- PG18 reservoir/sampling ----
4690  f!(
4691    "random_normal",
4692    "random_normal([mean double, stddev double]) -> double precision",
4693    "Normally-distributed random number (PG17+). Default mean 0, stddev 1.",
4694    "SELECT random_normal(0.0, 1.0);",
4695    pg("functions-math.html")
4696  );
4697  // ---- pgvector ----
4698  f!(
4699    "cosine_distance",
4700    "cosine_distance(vector, vector) -> double precision",
4701    "1 - cosine similarity. Range [0, 2]. Requires `CREATE EXTENSION vector` (pgvector). Same as `<=>` operator.",
4702    "SELECT cosine_distance(embedding, '[0.1, 0.2, 0.3]') FROM docs ORDER BY 1 LIMIT 5;",
4703    pg("https://github.com/pgvector/pgvector")
4704  );
4705  f!(
4706    "l2_distance",
4707    "l2_distance(vector, vector) -> double precision",
4708    "Euclidean distance. Same as `<->` operator. pgvector.",
4709    "SELECT l2_distance(embedding, query) FROM docs ORDER BY 1 LIMIT 5;",
4710    pg("https://github.com/pgvector/pgvector")
4711  );
4712  f!(
4713    "inner_product",
4714    "inner_product(vector, vector) -> double precision",
4715    "Dot product. Negated form (`<#>`) sorts low->high for KNN. pgvector.",
4716    "SELECT inner_product(a, b) FROM vecs;",
4717    pg("https://github.com/pgvector/pgvector")
4718  );
4719  f!(
4720    "l1_distance",
4721    "l1_distance(vector, vector) -> double precision",
4722    "Taxicab (Manhattan) distance. pgvector 0.5+.",
4723    "SELECT l1_distance(a, b) FROM vecs;",
4724    pg("https://github.com/pgvector/pgvector")
4725  );
4726  f!(
4727    "vector_dims",
4728    "vector_dims(vector) -> int",
4729    "Dimensionality of the vector.",
4730    "SELECT vector_dims(embedding) FROM docs LIMIT 1;",
4731    pg("https://github.com/pgvector/pgvector")
4732  );
4733  f!(
4734    "vector_norm",
4735    "vector_norm(vector) -> double precision",
4736    "L2 norm (magnitude). Use to normalize.",
4737    "SELECT embedding / vector_norm(embedding) FROM docs;",
4738    pg("https://github.com/pgvector/pgvector")
4739  );
4740  f!(
4741    "hamming_distance",
4742    "hamming_distance(bit, bit) -> int",
4743    "Bit-wise hamming distance. pgvector also exposes a vector variant.",
4744    "SELECT hamming_distance('1010', '1100');",
4745    pg("https://github.com/pgvector/pgvector")
4746  );
4747  f!(
4748    "jaccard_distance",
4749    "jaccard_distance(bit, bit) -> double precision",
4750    "1 - Jaccard similarity. pgvector for binary vectors.",
4751    "SELECT jaccard_distance('1010', '1100');",
4752    pg("https://github.com/pgvector/pgvector")
4753  );
4754  // ---- hstore (CREATE EXTENSION hstore) ----
4755  f!(
4756    "akeys",
4757    "akeys(hstore) -> text[]",
4758    "Return all keys as a text array (sorted by storage order).",
4759    "SELECT akeys('a=>1,b=>2'::hstore);",
4760    pg("hstore.html")
4761  );
4762  f!(
4763    "avals",
4764    "avals(hstore) -> text[]",
4765    "Return all values as a text array.",
4766    "SELECT avals('a=>1,b=>2'::hstore);",
4767    pg("hstore.html")
4768  );
4769  f!(
4770    "skeys",
4771    "skeys(hstore) -> setof text",
4772    "Return keys as a row set.",
4773    "SELECT * FROM skeys('a=>1,b=>2'::hstore);",
4774    pg("hstore.html")
4775  );
4776  f!(
4777    "svals",
4778    "svals(hstore) -> setof text",
4779    "Return values as a row set.",
4780    "SELECT * FROM svals('a=>1,b=>2'::hstore);",
4781    pg("hstore.html")
4782  );
4783  f!(
4784    "hstore_to_json",
4785    "hstore_to_json(hstore) -> json",
4786    "Convert hstore to JSON.",
4787    "SELECT hstore_to_json('a=>1'::hstore);",
4788    pg("hstore.html")
4789  );
4790  f!(
4791    "hstore_to_jsonb",
4792    "hstore_to_jsonb(hstore) -> jsonb",
4793    "Convert hstore to JSONB (faster for storage/queries).",
4794    "SELECT hstore_to_jsonb('a=>1'::hstore);",
4795    pg("hstore.html")
4796  );
4797  f!(
4798    "hstore_to_array",
4799    "hstore_to_array(hstore) -> text[]",
4800    "Flatten hstore to {k1, v1, k2, v2, ...} text array.",
4801    "SELECT hstore_to_array('a=>1,b=>2'::hstore);",
4802    pg("hstore.html")
4803  );
4804  f!(
4805    "hstore_to_matrix",
4806    "hstore_to_matrix(hstore) -> text[][]",
4807    "Convert hstore to a 2-dim array {{k,v},{k,v}}.",
4808    "SELECT hstore_to_matrix('a=>1,b=>2'::hstore);",
4809    pg("hstore.html")
4810  );
4811  f!(
4812    "populate_record",
4813    "populate_record(base anyelement, hstore) -> anyelement",
4814    "Cast hstore into a record/row type, using `base` for shape.",
4815    "SELECT populate_record(NULL::users, 'id=>1,name=>x'::hstore);",
4816    pg("hstore.html")
4817  );
4818  f!(
4819    "each_hstore",
4820    "each(hstore) -> setof record(key text, value text)",
4821    "Decompose hstore into (key, value) rows.",
4822    "SELECT * FROM each('a=>1,b=>2'::hstore);",
4823    pg("hstore.html")
4824  );
4825  f!(
4826    "exist",
4827    "exist(hstore, key text) -> boolean",
4828    "True when the hstore has the given key. Same as `?` operator.",
4829    "SELECT exist(meta, 'priority') FROM jobs;",
4830    pg("hstore.html")
4831  );
4832  f!(
4833    "defined",
4834    "defined(hstore, key text) -> boolean",
4835    "True when key exists AND its value is not NULL.",
4836    "SELECT defined(meta, 'tag') FROM jobs;",
4837    pg("hstore.html")
4838  );
4839  // ---- pg_stat_statements monitoring helper ----
4840  f!(
4841    "pg_stat_statements",
4842    "pg_stat_statements [(showtext boolean)] -> setof record(queryid, userid, dbid, calls, total_exec_time, mean_exec_time, rows, ...)",
4843    "Per-statement execution stats. Requires `CREATE EXTENSION pg_stat_statements` and `shared_preload_libraries`.",
4844    "SELECT queryid, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;",
4845    pg("pgstatstatements.html")
4846  );
4847  f!(
4848    "pg_stat_statements_reset",
4849    "pg_stat_statements_reset([userid oid, dbid oid, queryid bigint]) -> setof bigint",
4850    "Reset stats for all entries, or just matching subset (PG14+).",
4851    "SELECT pg_stat_statements_reset();",
4852    pg("pgstatstatements.html")
4853  );
4854  f!(
4855    "pg_stat_statements_info",
4856    "pg_stat_statements_info() -> record(dealloc bigint, stats_reset timestamptz)",
4857    "Sidecar stats for pg_stat_statements itself (PG14+).",
4858    "SELECT * FROM pg_stat_statements_info();",
4859    pg("pgstatstatements.html")
4860  );
4861  // ---- PostGIS basic geometry constructors ----
4862  f!(
4863    "st_geomfromtext",
4864    "ST_GeomFromText(wkt text [, srid int]) -> geometry",
4865    "Build a geometry from Well-Known Text. PostGIS.",
4866    "SELECT ST_GeomFromText('POINT(-71.064544 42.28787)', 4326);",
4867    pg("https://postgis.net/docs/ST_GeomFromText.html")
4868  );
4869  f!(
4870    "st_geomfromewkt",
4871    "ST_GeomFromEWKT(text) -> geometry",
4872    "EWKT variant that includes the SRID inline. PostGIS.",
4873    "SELECT ST_GeomFromEWKT('SRID=4326;POINT(-71 42)');",
4874    pg("https://postgis.net/docs/ST_GeomFromEWKT.html")
4875  );
4876  f!(
4877    "st_geomfromwkb",
4878    "ST_GeomFromWKB(bytea [, srid int]) -> geometry",
4879    "Build a geometry from Well-Known Binary. PostGIS.",
4880    "SELECT ST_GeomFromWKB(decode('...', 'hex'), 4326);",
4881    pg("https://postgis.net/docs/ST_GeomFromWKB.html")
4882  );
4883  f!(
4884    "st_point",
4885    "ST_Point(x double, y double [, srid int]) -> geometry",
4886    "Build a point. PostGIS 3.2+ adds the srid form; older versions need ST_SetSRID.",
4887    "SELECT ST_Point(-71.0, 42.0, 4326);",
4888    pg("https://postgis.net/docs/ST_Point.html")
4889  );
4890  f!(
4891    "st_makepoint",
4892    "ST_MakePoint(x double, y double [, z, m]) -> geometry",
4893    "Same as ST_Point pre-3.2.",
4894    "SELECT ST_MakePoint(-71.0, 42.0);",
4895    pg("https://postgis.net/docs/ST_MakePoint.html")
4896  );
4897  f!(
4898    "st_setsrid",
4899    "ST_SetSRID(geom geometry, srid int) -> geometry",
4900    "Stamp an SRID onto a geometry (no reprojection). PostGIS.",
4901    "SELECT ST_SetSRID(ST_MakePoint(-71, 42), 4326);",
4902    pg("https://postgis.net/docs/ST_SetSRID.html")
4903  );
4904  f!(
4905    "st_transform",
4906    "ST_Transform(geom geometry, to_srid int) -> geometry",
4907    "Reproject between coordinate systems. PostGIS.",
4908    "SELECT ST_Transform(geom, 3857) FROM places;",
4909    pg("https://postgis.net/docs/ST_Transform.html")
4910  );
4911  // ---- PostGIS introspection ----
4912  f!(
4913    "st_astext",
4914    "ST_AsText(geom geometry [, maxdecimals int]) -> text",
4915    "Format geometry as WKT. PostGIS.",
4916    "SELECT ST_AsText(geom) FROM places LIMIT 1;",
4917    pg("https://postgis.net/docs/ST_AsText.html")
4918  );
4919  f!(
4920    "st_asewkt",
4921    "ST_AsEWKT(geom geometry) -> text",
4922    "WKT with SRID prefix. PostGIS.",
4923    "SELECT ST_AsEWKT(geom) FROM places LIMIT 1;",
4924    pg("https://postgis.net/docs/ST_AsEWKT.html")
4925  );
4926  f!(
4927    "st_asgeojson",
4928    "ST_AsGeoJSON(geom geometry [, maxdecimals int, options int]) -> text",
4929    "Format as GeoJSON. PostGIS.",
4930    "SELECT ST_AsGeoJSON(geom, 6) FROM places;",
4931    pg("https://postgis.net/docs/ST_AsGeoJSON.html")
4932  );
4933  f!(
4934    "st_srid",
4935    "ST_SRID(geom geometry) -> int",
4936    "Spatial reference system id. 0 means unspecified.",
4937    "SELECT ST_SRID(geom) FROM places LIMIT 1;",
4938    pg("https://postgis.net/docs/ST_SRID.html")
4939  );
4940  f!(
4941    "st_geometrytype",
4942    "ST_GeometryType(geom geometry) -> text",
4943    "'ST_Point', 'ST_LineString', etc. Prefixed.",
4944    "SELECT ST_GeometryType(geom) FROM places;",
4945    pg("https://postgis.net/docs/ST_GeometryType.html")
4946  );
4947  // ---- PostGIS spatial predicates ----
4948  f!(
4949    "st_within",
4950    "ST_Within(a geometry, b geometry) -> boolean",
4951    "True when a is completely inside b. PostGIS. Indexed by GiST when the args have spatial indexes.",
4952    "SELECT * FROM places p WHERE ST_Within(p.geom, ST_GeomFromText('POLYGON(...)', 4326));",
4953    pg("https://postgis.net/docs/ST_Within.html")
4954  );
4955  f!(
4956    "st_contains",
4957    "ST_Contains(a, b geometry) -> boolean",
4958    "Mirror of ST_Within. PostGIS.",
4959    "SELECT ST_Contains(poly, point) FROM ...;",
4960    pg("https://postgis.net/docs/ST_Contains.html")
4961  );
4962  f!(
4963    "st_intersects",
4964    "ST_Intersects(a, b geometry) -> boolean",
4965    "True when geometries share at least one point. PostGIS.",
4966    "SELECT * FROM roads WHERE ST_Intersects(geom, ST_Buffer(town_geom, 100));",
4967    pg("https://postgis.net/docs/ST_Intersects.html")
4968  );
4969  f!(
4970    "st_disjoint",
4971    "ST_Disjoint(a, b geometry) -> boolean",
4972    "Mirror of ST_Intersects.",
4973    "SELECT ST_Disjoint(a, b) FROM ...;",
4974    pg("https://postgis.net/docs/ST_Disjoint.html")
4975  );
4976  f!(
4977    "st_dwithin",
4978    "ST_DWithin(a, b geometry, distance double precision [, use_spheroid boolean]) -> boolean",
4979    "True when geometries are within `distance` meters. Crucial for KNN/radius queries. PostGIS.",
4980    "SELECT * FROM places WHERE ST_DWithin(geom::geography, ST_MakePoint(lon, lat)::geography, 1000);",
4981    pg("https://postgis.net/docs/ST_DWithin.html")
4982  );
4983  f!(
4984    "st_distance",
4985    "ST_Distance(a, b geometry) -> double precision | ST_Distance(a, b geography [, use_spheroid]) -> double precision (meters)",
4986    "Shortest distance between geometries.",
4987    "SELECT ST_Distance(a, b) FROM pairs;",
4988    pg("https://postgis.net/docs/ST_Distance.html")
4989  );
4990  f!(
4991    "st_area",
4992    "ST_Area(geom geometry) -> double precision | ST_Area(geog geography) -> double precision (m²)",
4993    "Surface area. Casting to geography gives accurate metric results.",
4994    "SELECT ST_Area(geom::geography) FROM polygons;",
4995    pg("https://postgis.net/docs/ST_Area.html")
4996  );
4997  f!(
4998    "st_length",
4999    "ST_Length(geom geometry) -> double precision | (geography) -> meters",
5000    "Total length of linestrings.",
5001    "SELECT ST_Length(geom::geography) FROM roads;",
5002    pg("https://postgis.net/docs/ST_Length.html")
5003  );
5004  f!(
5005    "st_buffer",
5006    "ST_Buffer(geom geometry, distance double [, quad_segs int | options text]) -> geometry",
5007    "Expand geometry by `distance` (in units of SRID, or meters when cast to geography).",
5008    "SELECT ST_Buffer(point::geography, 1000) FROM places;",
5009    pg("https://postgis.net/docs/ST_Buffer.html")
5010  );
5011  f!(
5012    "st_centroid",
5013    "ST_Centroid(geom geometry) -> geometry",
5014    "Geometric centroid (center of mass).",
5015    "SELECT ST_Centroid(geom) FROM polygons;",
5016    pg("https://postgis.net/docs/ST_Centroid.html")
5017  );
5018  f!(
5019    "st_makeenvelope",
5020    "ST_MakeEnvelope(xmin double, ymin double, xmax double, ymax double [, srid int]) -> geometry",
5021    "Build a rectangular polygon (bounding box).",
5022    "SELECT ST_MakeEnvelope(-180, -90, 180, 90, 4326);",
5023    pg("https://postgis.net/docs/ST_MakeEnvelope.html")
5024  );
5025  // ---- pg_partman ----
5026  f!(
5027    "create_parent",
5028    "partman.create_parent(p_parent_table text, p_control text, p_type text, p_interval text, ...) -> boolean",
5029    "Bootstrap a partition set + its background trigger. pg_partman.",
5030    "SELECT partman.create_parent('public.events', 'created_at', 'native', 'monthly');",
5031    pg("https://github.com/pgpartman/pg_partman")
5032  );
5033  f!(
5034    "run_maintenance",
5035    "partman.run_maintenance([p_parent_table text]) -> void",
5036    "Create new partitions ahead of time, drop old ones. Called by cron. pg_partman.",
5037    "SELECT partman.run_maintenance('public.events');",
5038    pg("https://github.com/pgpartman/pg_partman")
5039  );
5040  f!(
5041    "partition_data_time",
5042    "partman.partition_data_time(p_parent_table text, p_batch_count int, ...) -> bigint",
5043    "Move rows from a default partition into the right child. pg_partman.",
5044    "SELECT partman.partition_data_time('public.events');",
5045    pg("https://github.com/pgpartman/pg_partman")
5046  );
5047  // ---- pg_cron ----
5048  f!(
5049    "cron_schedule",
5050    "cron.schedule(job_name text, schedule text, command text) -> bigint",
5051    "Schedule a SQL command on a 5-field cron string. Returns the new job id. pg_cron.",
5052    "SELECT cron.schedule('clean-old-events', '0 3 * * *', $$ DELETE FROM events WHERE ts < now() - INTERVAL '90 days' $$);",
5053    pg("https://github.com/citusdata/pg_cron")
5054  );
5055  f!(
5056    "cron_unschedule",
5057    "cron.unschedule(jobid bigint | job_name text) -> boolean",
5058    "Cancel a scheduled job. pg_cron.",
5059    "SELECT cron.unschedule('clean-old-events');",
5060    pg("https://github.com/citusdata/pg_cron")
5061  );
5062  f!(
5063    "cron_schedule_in_database",
5064    "cron.schedule_in_database(job_name text, schedule text, command text, database text, [user text, active boolean]) -> bigint",
5065    "Schedule a job that runs against a specific database. pg_cron.",
5066    "SELECT cron.schedule_in_database('analytics-refresh', '0 * * * *', 'REFRESH MATERIALIZED VIEW mv', 'analytics');",
5067    pg("https://github.com/citusdata/pg_cron")
5068  );
5069  // ---- TimescaleDB hypertables ----
5070  f!(
5071    "create_hypertable",
5072    "create_hypertable(relation regclass, time_column_name name [, chunk_time_interval interval, ...]) -> record",
5073    "Convert a regular table into a TimescaleDB hypertable; partitions on time. Many optional knobs (space partitioning, migrate_data).",
5074    "SELECT create_hypertable('events', 'ts', chunk_time_interval => INTERVAL '1 day');",
5075    pg("https://docs.timescale.com/api/latest/hypertable/create_hypertable/")
5076  );
5077  f!(
5078    "add_dimension",
5079    "add_dimension(hypertable regclass, column_name name [, number_partitions int | chunk_time_interval interval]) -> record",
5080    "Add an additional partitioning dimension (e.g. space partition).",
5081    "SELECT add_dimension('events', 'region_id', number_partitions => 4);",
5082    pg("https://docs.timescale.com/api/latest/hypertable/add_dimension/")
5083  );
5084  f!(
5085    "add_compression_policy",
5086    "add_compression_policy(hypertable regclass, compress_after interval, [if_not_exists boolean]) -> int",
5087    "Auto-compress chunks older than `compress_after`. TimescaleDB.",
5088    "SELECT add_compression_policy('events', INTERVAL '30 days');",
5089    pg("https://docs.timescale.com/api/latest/compression/add_compression_policy/")
5090  );
5091  f!(
5092    "add_retention_policy",
5093    "add_retention_policy(hypertable regclass, drop_after interval, [if_not_exists boolean]) -> int",
5094    "Auto-drop chunks older than `drop_after`. TimescaleDB.",
5095    "SELECT add_retention_policy('events', INTERVAL '1 year');",
5096    pg("https://docs.timescale.com/api/latest/data_retention/add_retention_policy/")
5097  );
5098  f!(
5099    "add_continuous_aggregate_policy",
5100    "add_continuous_aggregate_policy(cagg regclass, start_offset interval, end_offset interval, schedule_interval interval) -> int",
5101    "Schedule incremental refresh of a continuous aggregate (materialized view of time bucket aggregates).",
5102    "SELECT add_continuous_aggregate_policy('events_5m', INTERVAL '1 month', INTERVAL '5 minutes', INTERVAL '5 minutes');",
5103    pg("https://docs.timescale.com/api/latest/continuous-aggregates/add_continuous_aggregate_policy/")
5104  );
5105  f!(
5106    "time_bucket",
5107    "time_bucket(bucket_width interval, ts timestamp [, origin timestamp]) -> timestamp",
5108    "Snap timestamp to the nearest bucket. TimescaleDB.",
5109    "SELECT time_bucket('5 minutes', ts), avg(value) FROM metrics GROUP BY 1;",
5110    pg("https://docs.timescale.com/api/latest/hyperfunctions/time_bucket/")
5111  );
5112  f!(
5113    "time_bucket_gapfill",
5114    "time_bucket_gapfill(bucket_width interval, ts timestamp, start timestamp, finish timestamp) -> timestamp",
5115    "time_bucket + gap-fill missing buckets in [start, finish] range. TimescaleDB.",
5116    "SELECT time_bucket_gapfill('5 minutes', ts, now() - INTERVAL '1 hour', now()), locf(avg(value)) FROM metrics GROUP BY 1;",
5117    pg("https://docs.timescale.com/api/latest/hyperfunctions/gapfilling/time_bucket_gapfill/")
5118  );
5119  f!(
5120    "show_chunks",
5121    "show_chunks(hypertable regclass [, older_than interval, newer_than interval]) -> setof regclass",
5122    "List chunk OIDs of a hypertable.",
5123    "SELECT show_chunks('events', older_than => INTERVAL '7 days');",
5124    pg("https://docs.timescale.com/api/latest/hypertable/show_chunks/")
5125  );
5126  f!(
5127    "drop_chunks",
5128    "drop_chunks(hypertable regclass, older_than interval) -> setof text",
5129    "Drop chunks older than the cutoff. TimescaleDB.",
5130    "SELECT drop_chunks('events', INTERVAL '1 year');",
5131    pg("https://docs.timescale.com/api/latest/data_retention/drop_chunks/")
5132  );
5133  f!(
5134    "locf",
5135    "locf(value anyelement) -> anyelement",
5136    "Last-Observation-Carried-Forward fill; pair with time_bucket_gapfill. TimescaleDB.",
5137    "SELECT time_bucket_gapfill('5m', ts, ...), locf(avg(value)) FROM metrics GROUP BY 1;",
5138    pg("https://docs.timescale.com/api/latest/hyperfunctions/gapfilling/locf/")
5139  );
5140  f!(
5141    "interpolate",
5142    "interpolate(value anyelement) -> double precision",
5143    "Linear interpolation of gap values. TimescaleDB.",
5144    "SELECT time_bucket_gapfill('5m', ts, ...), interpolate(avg(value)) FROM metrics GROUP BY 1;",
5145    pg("https://docs.timescale.com/api/latest/hyperfunctions/gapfilling/interpolate/")
5146  );
5147  f!(
5148    "first",
5149    "first(value anyelement, time timestamp) -> anyelement",
5150    "Return `value` from the row with the smallest `time`. TimescaleDB.",
5151    "SELECT first(metric, ts) FROM events;",
5152    pg("https://docs.timescale.com/api/latest/hyperfunctions/first/")
5153  );
5154  f!(
5155    "last",
5156    "last(value anyelement, time timestamp) -> anyelement",
5157    "Return `value` from the row with the largest `time`. TimescaleDB.",
5158    "SELECT last(metric, ts) FROM events;",
5159    pg("https://docs.timescale.com/api/latest/hyperfunctions/last/")
5160  );
5161  // ---- Citus distribution helpers ----
5162  f!(
5163    "create_distributed_table",
5164    "create_distributed_table(table_name regclass, distribution_column text [, distribution_type text, colocate_with text]) -> void",
5165    "Shard a table by the chosen column. Citus.",
5166    "SELECT create_distributed_table('events', 'user_id');",
5167    pg("https://docs.citusdata.com/en/stable/develop/api_udf.html#create-distributed-table")
5168  );
5169  f!(
5170    "create_reference_table",
5171    "create_reference_table(table_name regclass) -> void",
5172    "Replicate a small reference table to every worker. Citus.",
5173    "SELECT create_reference_table('countries');",
5174    pg("https://docs.citusdata.com/en/stable/develop/api_udf.html#create-reference-table")
5175  );
5176  f!(
5177    "citus_add_node",
5178    "citus_add_node(node_name text, node_port int [, node_group int, ...]) -> int",
5179    "Register a new Citus worker.",
5180    "SELECT citus_add_node('worker-3', 5432);",
5181    pg("https://docs.citusdata.com/en/stable/admin_guide/cluster_management.html")
5182  );
5183  f!(
5184    "citus_remove_node",
5185    "citus_remove_node(node_name text, node_port int) -> void",
5186    "Deregister a worker. Drains shards first.",
5187    "SELECT citus_remove_node('worker-3', 5432);",
5188    pg("https://docs.citusdata.com/en/stable/admin_guide/cluster_management.html")
5189  );
5190  f!(
5191    "citus_shards",
5192    "citus_shards -- view exposing shard placement / size.",
5193    "SELECT * FROM citus_shards ORDER BY shard_size DESC LIMIT 5;",
5194    "SELECT * FROM citus_shards LIMIT 5;",
5195    pg("https://docs.citusdata.com/en/stable/admin_guide/cluster_management.html")
5196  );
5197  f!(
5198    "citus_rebalance_start",
5199    "citus_rebalance_start([rebalance_strategy text, drain_only boolean, shard_transfer_mode text]) -> bigint",
5200    "Kick off background rebalance. Citus.",
5201    "SELECT citus_rebalance_start();",
5202    pg("https://docs.citusdata.com/en/stable/admin_guide/cluster_management.html")
5203  );
5204  // ---- PostgresML inference ----
5205  f!(
5206    "pgml_predict",
5207    "pgml.predict(project text, features ...) -> double precision | anyarray",
5208    "Run inference using a registered model. PostgresML.",
5209    "SELECT pgml.predict('churn_model', user_features) FROM users;",
5210    pg("https://postgresml.org/docs")
5211  );
5212  f!(
5213    "pgml_embed",
5214    "pgml.embed(transformer text, text text [, kwargs jsonb]) -> real[]",
5215    "Compute a text embedding using a HuggingFace model. PostgresML.",
5216    "SELECT pgml.embed('intfloat/e5-small', 'hello world');",
5217    pg("https://postgresml.org/docs")
5218  );
5219  f!(
5220    "pgml_chat",
5221    "pgml.transform(task jsonb | text, inputs text[]) -> jsonb",
5222    "Run a HuggingFace pipeline (chat, summarization, classification). PostgresML.",
5223    "SELECT pgml.transform('summarization', ARRAY['<long text>']);",
5224    pg("https://postgresml.org/docs")
5225  );
5226  f!(
5227    "bit_xor",
5228    "bit_xor(int|bigint|bit) -> same",
5229    "Aggregate: bitwise XOR across rows. PG14+.",
5230    "SELECT bit_xor(flags) FROM events;",
5231    pg("functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE")
5232  );
5233  f!(
5234    "enum_first",
5235    "enum_first(anyenum) -> anyenum",
5236    "First label of an enum type. Pass `NULL::<enum_t>` to specify type.",
5237    "SELECT enum_first(NULL::mood);",
5238    pg("functions-enum.html")
5239  );
5240  f!(
5241    "enum_last",
5242    "enum_last(anyenum) -> anyenum",
5243    "Last label of an enum type.",
5244    "SELECT enum_last(NULL::mood);",
5245    pg("functions-enum.html")
5246  );
5247  f!(
5248    "enum_range",
5249    "enum_range([from anyenum,] [to anyenum]) -> anyenum[]",
5250    "All labels of an enum (with optional inclusive bounds).",
5251    "SELECT enum_range('sad'::mood, 'happy'::mood);",
5252    pg("functions-enum.html")
5253  );
5254  f!(
5255    "json_array",
5256    "JSON_ARRAY(<expr>[, ...]) -> json",
5257    "SQL:2023 array constructor (PG16+).",
5258    "SELECT JSON_ARRAY(1, 'two', true);",
5259    pg("functions-json.html#FUNCTIONS-SQLJSON-CREATION")
5260  );
5261  f!(
5262    "json_object",
5263    "JSON_OBJECT(<key>: <val>[, ...] [ABSENT|NULL ON NULL] [UNIQUE]) -> json",
5264    "SQL:2023 object constructor (PG16+).",
5265    "SELECT JSON_OBJECT('id': 1, 'name': 'a');",
5266    pg("functions-json.html#FUNCTIONS-SQLJSON-CREATION")
5267  );
5268  f!(
5269    "json_scalar",
5270    "JSON_SCALAR(<expr>) -> json",
5271    "SQL:2023 scalar constructor; promotes value to JSON.",
5272    "SELECT JSON_SCALAR(42);",
5273    pg("functions-json.html#FUNCTIONS-SQLJSON-CREATION")
5274  );
5275  f!(
5276    "json_serialize",
5277    "JSON_SERIALIZE(<json> [RETURNING <type>] [PRETTY]) -> text|bytea",
5278    "SQL:2023 JSON_SERIALIZE -- render a jsonb back to text.",
5279    "SELECT JSON_SERIALIZE('{\"a\":1}'::jsonb RETURNING text PRETTY);",
5280    pg("functions-json.html#FUNCTIONS-SQLJSON-PROCESSING")
5281  );
5282  f!(
5283    "jsonb_insert",
5284    "jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean]) -> jsonb",
5285    "Insert into nested location at `path`. `insert_after` (default false) controls order around an existing array index.",
5286    "SELECT jsonb_insert('[1,2,3]'::jsonb, '{1}', '99'::jsonb, false);",
5287    pg("functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE")
5288  );
5289
5290  // ---- cycle2 round 73: remaining admin / xact / signal helpers ----
5291  f!(
5292    "pg_xact_status",
5293    "pg_xact_status(xid8) -> text",
5294    "Status of a transaction id: 'in progress' / 'committed' / 'aborted'. PG10+.",
5295    "SELECT pg_xact_status(pg_current_xact_id());",
5296    pg("functions-info.html#FUNCTIONS-INFO-SNAPSHOT")
5297  );
5298  f!(
5299    "pg_current_xact_id",
5300    "pg_current_xact_id() -> xid8",
5301    "Current transaction id (8-byte). Assigns one if none yet.",
5302    "SELECT pg_current_xact_id();",
5303    pg("functions-info.html#FUNCTIONS-INFO-SNAPSHOT")
5304  );
5305  f!(
5306    "pg_log_standby_snapshot",
5307    "pg_log_standby_snapshot() -> pg_lsn",
5308    "Trigger a standby snapshot WAL record; used to let logical replication slots advance on a quiet system. PG14+.",
5309    "SELECT pg_log_standby_snapshot();",
5310    pg("functions-admin.html#FUNCTIONS-ADMIN-SIGNALING")
5311  );
5312  f!(
5313    "pg_trigger_depth",
5314    "pg_trigger_depth() -> int",
5315    "Current nesting depth of trigger invocations (0 outside triggers, 1 inside the outermost).",
5316    "SELECT pg_trigger_depth();",
5317    pg("functions-info.html#FUNCTIONS-INFO-SESSION")
5318  );
5319  f!(
5320    "pg_log_query_plan",
5321    "pg_log_query_plan(pid int) -> boolean",
5322    "Ask another backend to log its currently-executing query plan. PG16+.",
5323    "SELECT pg_log_query_plan(12345);",
5324    pg("functions-admin.html#FUNCTIONS-ADMIN-SIGNALING")
5325  );
5326  f!(
5327    "pg_stat_have_stats",
5328    "pg_stat_have_stats(stat_kind text, objoid oid, subobjoid int) -> boolean",
5329    "Ask whether a stats entry exists for a given object. PG15+.",
5330    "SELECT pg_stat_have_stats('relation', 'users'::regclass::oid, 0);",
5331    pg("monitoring-stats.html#MONITORING-STATS-FUNCTIONS")
5332  );
5333  f!(
5334    "pg_stat_reset_subscription_stats",
5335    "pg_stat_reset_subscription_stats(subid oid) -> void",
5336    "Reset cumulative subscription stats counters. PG15+.",
5337    "SELECT pg_stat_reset_subscription_stats(NULL); -- all subscriptions",
5338    pg("monitoring-stats.html#MONITORING-STATS-FUNCTIONS")
5339  );
5340  f!(
5341    "pg_stat_reset_slru",
5342    "pg_stat_reset_slru(target text) -> void",
5343    "Reset SLRU cache stats counters (CommitTs, MultiXactMember, ...). PG13+.",
5344    "SELECT pg_stat_reset_slru(NULL); -- all SLRUs",
5345    pg("monitoring-stats.html#MONITORING-STATS-FUNCTIONS")
5346  );
5347  f!(
5348    "pg_settings_get_flags",
5349    "pg_settings_get_flags(guc_name text) -> text[]",
5350    "Introspect categorical flags on a GUC (NO_SHOW_ALL, NO_RESET_ALL, NOT_IN_SAMPLE, ...). PG15+.",
5351    "SELECT pg_settings_get_flags('work_mem');",
5352    pg("functions-admin.html#FUNCTIONS-ADMIN-SET")
5353  );
5354  f!(
5355    "pg_split_walfile_name",
5356    "pg_split_walfile_name(name text) -> record",
5357    "Decompose a WAL filename into (segment_number, timeline_id). PG17+.",
5358    "SELECT * FROM pg_split_walfile_name('000000010000000000000001');",
5359    pg("functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE")
5360  );
5361  f!(
5362    "pg_get_acl",
5363    "pg_get_acl(classid oid, objid oid, objsubid int) -> aclitem[]",
5364    "Return the ACL of any cataloged object as the raw aclitem array. PG17+.",
5365    "SELECT pg_get_acl('pg_class'::regclass::oid, 'users'::regclass::oid, 0);",
5366    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
5367  );
5368  f!(
5369    "pg_get_loaded_modules",
5370    "pg_get_loaded_modules() -> setof record",
5371    "List of currently loaded shared libraries (name, version, modules). PG17+.",
5372    "SELECT * FROM pg_get_loaded_modules();",
5373    pg("functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT")
5374  );
5375  f!(
5376    "pg_basetype",
5377    "pg_basetype(regtype) -> regtype",
5378    "Strip domain wrappers and return the underlying base type. PG17+.",
5379    "SELECT pg_basetype('positive_int'::regtype);",
5380    pg("functions-info.html#FUNCTIONS-INFO-CATALOG")
5381  );
5382  f!(
5383    "pg_input_error_message",
5384    "pg_input_error_message(input text, type text) -> text",
5385    "Like `pg_input_is_valid` but returns the error text on failure (NULL on success). PG16+.",
5386    "SELECT pg_input_error_message('foo', 'int');",
5387    pg("functions-info.html#FUNCTIONS-INFO-VALIDITY")
5388  );
5389  f!(
5390    "pg_signal_backend",
5391    "pg_signal_backend(pid int, sig text) -> boolean",
5392    "Lower-level cousin of `pg_cancel_backend`/`pg_terminate_backend`. Restricted by pg_signal_backend role.",
5393    "SELECT pg_signal_backend(12345, 'SIGINT');",
5394    pg("functions-admin.html#FUNCTIONS-ADMIN-SIGNALING")
5395  );
5396  f!(
5397    "pg_xlog_replay_pause",
5398    "pg_xlog_replay_pause() -> void",
5399    "Legacy alias of `pg_wal_replay_pause` (PG9.x). Removed in newer versions; use `pg_wal_replay_pause`.",
5400    "SELECT pg_wal_replay_pause();",
5401    pg("functions-admin.html#FUNCTIONS-RECOVERY-CONTROL")
5402  );
5403  f!(
5404    "pg_xlogfile_name",
5405    "pg_xlogfile_name(lsn pg_lsn) -> text",
5406    "Legacy alias of `pg_walfile_name`. Use `pg_walfile_name`.",
5407    "SELECT pg_walfile_name(pg_current_wal_lsn());",
5408    pg("functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE")
5409  );
5410  f!(
5411    "inet_send",
5412    "inet_send(inet) -> bytea",
5413    "Type-output (binary) function for `inet`. Internal -- emitted by binary COPY/wire format.",
5414    "-- internal",
5415    pg("functions-net.html")
5416  );
5417
5418  m
5419}