1use 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 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 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 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 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 f!("chr", "chr(int) -> text", "Character for a Unicode code point.", "SELECT chr(65);", pg("functions-string.html"));
80
81 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 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 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 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 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 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 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 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 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 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 f!("sin", "sin(double precision) -> double precision", "Sine, radians.", "SELECT sin(0);", pg("functions-math.html"));
266
267 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 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 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 f!("ceil", "ceil(numeric) -> numeric", "Round up to integer.", "SELECT ceil(3.2);", pg("functions-math.html"));
318
319 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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}