konnosfunctions.ts114 lines · main
| 1 | import { safeSql } from '../pg-format' |
| 2 | |
| 3 | export const FUNCTIONS_SQL = /* SQL */ safeSql` |
| 4 | -- CTE with sane arg_modes, arg_names, and arg_types. |
| 5 | -- All three are always of the same length. |
| 6 | -- All three include all args, including OUT and TABLE args. |
| 7 | with functions as ( |
| 8 | select |
| 9 | *, |
| 10 | -- proargmodes is null when all arg modes are IN |
| 11 | coalesce( |
| 12 | p.proargmodes, |
| 13 | array_fill('i'::text, array[cardinality(coalesce(p.proallargtypes, p.proargtypes))]) |
| 14 | ) as arg_modes, |
| 15 | -- proargnames is null when all args are unnamed |
| 16 | coalesce( |
| 17 | p.proargnames, |
| 18 | array_fill(''::text, array[cardinality(coalesce(p.proallargtypes, p.proargtypes))]) |
| 19 | ) as arg_names, |
| 20 | -- proallargtypes is null when all arg modes are IN |
| 21 | coalesce(p.proallargtypes, p.proargtypes) as arg_types, |
| 22 | array_cat( |
| 23 | array_fill(false, array[pronargs - pronargdefaults]), |
| 24 | array_fill(true, array[pronargdefaults])) as arg_has_defaults |
| 25 | from |
| 26 | pg_proc as p |
| 27 | where |
| 28 | p.prokind = 'f' |
| 29 | ) |
| 30 | select |
| 31 | f.oid as id, |
| 32 | n.nspname as schema, |
| 33 | f.proname as name, |
| 34 | l.lanname as language, |
| 35 | case |
| 36 | when l.lanname = 'internal' then '' |
| 37 | else f.prosrc |
| 38 | end as definition, |
| 39 | case |
| 40 | when l.lanname = 'internal' then f.prosrc |
| 41 | else pg_get_functiondef(f.oid) |
| 42 | end as complete_statement, |
| 43 | coalesce(f_args.args, '[]') as args, |
| 44 | pg_get_function_arguments(f.oid) as argument_types, |
| 45 | pg_get_function_identity_arguments(f.oid) as identity_argument_types, |
| 46 | f.prorettype as return_type_id, |
| 47 | pg_get_function_result(f.oid) as return_type, |
| 48 | nullif(rt.typrelid, 0) as return_type_relation_id, |
| 49 | f.proretset as is_set_returning_function, |
| 50 | case |
| 51 | when f.provolatile = 'i' then 'IMMUTABLE' |
| 52 | when f.provolatile = 's' then 'STABLE' |
| 53 | when f.provolatile = 'v' then 'VOLATILE' |
| 54 | end as behavior, |
| 55 | f.prosecdef as security_definer, |
| 56 | f_config.config_params as config_params |
| 57 | from |
| 58 | functions f |
| 59 | left join pg_namespace n on f.pronamespace = n.oid |
| 60 | left join pg_language l on f.prolang = l.oid |
| 61 | left join pg_type rt on rt.oid = f.prorettype |
| 62 | left join ( |
| 63 | select |
| 64 | oid, |
| 65 | jsonb_object_agg(param, value) filter (where param is not null) as config_params |
| 66 | from |
| 67 | ( |
| 68 | select |
| 69 | oid, |
| 70 | (string_to_array(unnest(proconfig), '='))[1] as param, |
| 71 | (string_to_array(unnest(proconfig), '='))[2] as value |
| 72 | from |
| 73 | functions |
| 74 | ) as t |
| 75 | group by |
| 76 | oid |
| 77 | ) f_config on f_config.oid = f.oid |
| 78 | left join ( |
| 79 | select |
| 80 | oid, |
| 81 | jsonb_agg(jsonb_build_object( |
| 82 | 'mode', t2.mode, |
| 83 | 'name', name, |
| 84 | 'type_id', type_id, |
| 85 | -- Cast null into false boolean |
| 86 | 'has_default', COALESCE(has_default, false) |
| 87 | )) as args |
| 88 | from |
| 89 | ( |
| 90 | select |
| 91 | oid, |
| 92 | unnest(arg_modes) as mode, |
| 93 | unnest(arg_names) as name, |
| 94 | -- Coming from: coalesce(p.proallargtypes, p.proargtypes) postgres won't automatically assume |
| 95 | -- integer, we need to cast it to be properly parsed |
| 96 | unnest(arg_types)::int8 as type_id, |
| 97 | unnest(arg_has_defaults) as has_default |
| 98 | from |
| 99 | functions |
| 100 | ) as t1, |
| 101 | lateral ( |
| 102 | select |
| 103 | case |
| 104 | when t1.mode = 'i' then 'in' |
| 105 | when t1.mode = 'o' then 'out' |
| 106 | when t1.mode = 'b' then 'inout' |
| 107 | when t1.mode = 'v' then 'variadic' |
| 108 | else 'table' |
| 109 | end as mode |
| 110 | ) as t2 |
| 111 | group by |
| 112 | t1.oid |
| 113 | ) f_args on f_args.oid = f.oid |
| 114 | ` |