Functions only show functions for the public schema

[bombillazo]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

In the Supabase dashboard, when one creates a custom function for a schema other than public, they are created but not shown in the dashboard. We confirm that they exist by running:

SELECT
    routine_name
FROM 
    information_schema.routines
WHERE 
    routine_type = 'FUNCTION'
AND
    routine_schema = 'auth';

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. In the Supabase dashboard, go to 'Database -> Functions'
2. Click on 'Create new function'
3. In the schema, select a schema beside public (for example, the auth schema)
4. Fill in the rest of the 'Add a new function' form
5. In the functions dashboard, the function will not appear.
6. Run the SQL query above to confirm the function exists.

Expected behavior

I expect to see functions created in the non-public schema

Screenshots

Only the public schema functions are shown in the dashboard.

System information

• OS: All

[nrayburn-tech]

The functions that are displayed in the UI are filtered by a list of schemas. If this filter is removed, then all of the default available functions are displayed in the UI (something like 200+ from my test default project).

supabase/studio/stores/pgmeta/MetaStore.ts

Lines 172 to 189 in 4ed3e26

	// [Joshen] I'm going to treat this as a list of system schemas
	excludedSchemas = [
	'auth',
	'extensions',
	'information_schema',
	'net',
	'pgsodium',
	'pgsodium_masks',
	'pgbouncer',
	'pgtle',
	'realtime',
	'storage',
	'supabase_functions',
	'supabase_migrations',
	'vault',
	'graphql',
	'graphql_public',
	]

To remove the filter, updates need to be made to the below two places.

supabase/studio/components/interfaces/Database/Functions/FunctionsList/FunctionsList.tsx

Lines 28 to 30 in 4ed3e26

	const functions = meta.functions.list(
	(fn: PostgresFunction) => !meta.excludedSchemas.includes(fn.schema)
	)

supabase/studio/components/interfaces/Database/Functions/FunctionsList/FunctionList.tsx

Line 27 in 4ed3e26

const functions = meta.functions.list((fn: any) => !meta.excludedSchemas.includes(fn.schema))

There are a few options on how to fix this that I came up with below, but somebody from the Supabase team should weigh in on how it should be handled.

• Show all the functions, including the default functions. Can be done by just simply removing the filtering of excluded schemas.
• Show only user created functions. Would need some way to identify a function as user created, which would likely require some API change to the /api/pg-meta/default/functions endpoint so that it returns whether a function is user created or not.
• Hide the excluded schemas from the dropdown when creating functions. This way the user can only add functions to schemas that are shown in the functions list.

[bombillazo]

Hey @nrayburn-tech, thanks for the deep dive! A good solution would be to have a UI control that can dynamically update the filters so that not one is hardcoded and it gives users the options. The possible filters could be to show user-created schemas, default, or all.

[KTayy]

``
select n.nspname as schema_name,
p.proname as specific_name,
case p.prokind
when 'f' then 'FUNCTION'
when 'p' then 'PROCEDURE'
when 'a' then 'AGGREGATE'
when 'w' then 'WINDOW'
end as kind,
l.lanname as language,
case when l.lanname = 'internal' then p.prosrc
else pg_get_functiondef(p.oid)
end as definition,
pg_get_function_arguments(p.oid) as arguments,
t.typname as return_type
from pg_proc p
left join pg_namespace n on p.pronamespace = n.oid
left join pg_language l on p.prolang = l.oid
left join pg_type t on t.oid = p.prorettype
where
n.nspname not in ('pg_catalog', 'information_schema') and
p.proname = 'Name_of_your_function'
order by schema_name,
specific_name;
`

Hey, not sure if this is still an open question. I ran into a similar issue and this helped me. you can just copy the definition into an sql editor to get a readable version. If you do not know the name of your function just remove the p.proname where clause.