The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.
TypeScript aialternativeauthdatabasedenoembeddingsexamplefirebasenextjsoauth2pgvectorpostgispostgrespostgresqlpostgrestrealtimesupabasevectorswebsockets
import { safeSql } from '../pg-format'
export const FUNCTIONS_SQL = /* SQL */ safeSql`
-- CTE with sane arg_modes, arg_names, and arg_types.
-- All three are always of the same length.
-- All three include all args, including OUT and TABLE args.
with functions as (
select
*,
-- proargmodes is null when all arg modes are IN
coalesce(
p.proargmodes,
array_fill('i'::text, array[cardinality(coalesce(p.proallargtypes, p.proargtypes))])
) as arg_modes,
-- proargnames is null when all args are unnamed
coalesce(
p.proargnames,
array_fill(''::text, array[cardinality(coalesce(p.proallargtypes, p.proargtypes))])
) as arg_names,
-- proallargtypes is null when all arg modes are IN
coalesce(p.proallargtypes, p.proargtypes) as arg_types,
array_cat(
array_fill(false, array[pronargs - pronargdefaults]),
array_fill(true, array[pronargdefaults])) as arg_has_defaults
from
pg_proc as p
where
p.prokind in ('f', 'p')
)
select
f.oid as id,
n.nspname as schema,
f.proname as name,
l.lanname as language,
case f.prokind
when 'f' then 'function'
when 'p' then 'procedure'
when 'a' then 'aggregate'
when 'w' then 'window'
else 'unknown'
end as type,
case
when l.lanname = 'internal' then ''
else f.prosrc
end as definition,
case
when l.lanname = 'internal' then f.prosrc
else pg_get_functiondef(f.oid)
end as complete_statement,
coalesce(f_args.args, '[]') as args,
pg_get_function_arguments(f.oid) as argument_types,
pg_get_function_identity_arguments(f.oid) as identity_argument_types,
f.prorettype as return_type_id,
pg_get_function_result(f.oid) as return_type,
nullif(rt.typrelid, 0) as return_type_relation_id,
f.proretset as is_set_returning_function,
case
when f.provolatile = 'i' then 'IMMUTABLE'
when f.provolatile = 's' then 'STABLE'
when f.provolatile = 'v' then 'VOLATILE'
end as behavior,
f.prosecdef as security_definer,
f_config.config_params as config_params
from
functions f
left join pg_namespace n on f.pronamespace = n.oid
left join pg_language l on f.prolang = l.oid
left join pg_type rt on rt.oid = f.prorettype
left join (
select
oid,
jsonb_object_agg(param, value) filter (where param is not null) as config_params
from
(
select
oid,
(string_to_array(unnest(proconfig), '='))[1] as param,
(string_to_array(unnest(proconfig), '='))[2] as value
from
functions
) as t
group by
oid
) f_config on f_config.oid = f.oid
left join (
select
oid,
jsonb_agg(jsonb_build_object(
'mode', t2.mode,
'name', name,
'type_id', type_id,
-- Cast null into false boolean
'has_default', COALESCE(has_default, false)
)) as args
from
(
select
oid,
unnest(arg_modes) as mode,
unnest(arg_names) as name,
-- Coming from: coalesce(p.proallargtypes, p.proargtypes) postgres won't automatically assume
-- integer, we need to cast it to be properly parsed
unnest(arg_types)::int8 as type_id,
unnest(arg_has_defaults) as has_default
from
functions
) as t1,
lateral (
select
case
when t1.mode = 'i' then 'in'
when t1.mode = 'o' then 'out'
when t1.mode = 'b' then 'inout'
when t1.mode = 'v' then 'variadic'
else 'table'
end as mode
) as t2
group by
t1.oid
) f_args on f_args.oid = f.oid
`