CODEWITHSUNDEEP
postgresqlpgadmin
Matthew Allen
Matthew Allen

Reputation: 585

Is there a way to organize Postgres Functions (using pgAdmin)?

I'm using pgAdmin 4.23, PostgreSQL 12.3 for Windows

Looks like all functions are dumped into one "folder". I installed the uuid and tablefunc extensions and they get tossed in with my own user defined functions. At least the 10 uuid ones are all prefixed with "uuid_". The 11 tablefunc ones all start with "connectby", "crosstab"*, "normal_rand".

I did prefix my own functions so those at least grouped together. But as this thing grows and I add extensions, I'm concerned that maintenance will become more difficult. Is there some sort of sub-foldering option I am missing, or is naming convention the normal approach for organization? Looks like stored procs would work the same way.

Would also be nice to be able to filter the Functions based on the names. I see the Search Objects popup, but it isn't as useful as a filter.

Upvotes: 1

Views: 799

Answers (2)

Matthew Allen
Matthew Allen

Reputation: 585

I'm adding this here in case it helps for future searches...

Per @horse_with_no_name's suggestion above, and the official documentation I went with a separate schema for my third-party stuff. Here is a bit from conversation with the other db people in our company:

I didn't want third party extensions (like uuid-ossp for Guids) installed in my public schema since that is where we keep all of our user defined functions specific to that database. When I originally installed the extension I put it into the public schema, then just transferred it to a schema named extfunc. Then we have a table named usr that references extfunc.uuid_generate_v4() in one of the column constraints. Everything works as expected.

However, when I try to backup our Dev db and restore to QA, the pg_dump and pg_restore tasks do not handle it properly. The restore would error and not create the usr table. The extension was not being properly restored to extfunc, which prevented the usr table from being created.

The solution is to create the extension in the desired schema from the start. Do not create it and try and move it to the destination schema. Backup/restore now work as expected.

Upvotes: 0

Stefanov.sm
Stefanov.sm

Reputation: 13049

To filter functions based on their names you can use this function:

CREATE OR REPLACE FUNCTION public.find_function(fname text DEFAULT NULL::text)
 RETURNS TABLE(routine_name text, routine_schema text, return_type text)
 LANGUAGE sql
AS $function$
    select routine_name, routine_schema, data_type 
    from information_schema.routines 
    where specific_schema not in ('pg_catalog', 'information_schema')
    and case when fname is null then true else routine_name ~* fname end
    order by routine_name;
$function$;

Here is an example - find all functions that have "test" in their name:

select * from find_function('test');
+-----------------------------+----------------+-------------+  
| routine_name                | routine_schema | return_type |
+-----------------------------+----------------+-------------+  
| clear_web_tests             | datavato       | void        |
+-----------------------------+----------------+-------------+  
| etl_generic_tests           | webaccess      | text        |
+-----------------------------+----------------+-------------+  
| fill_web_tests              | datavato       | void        |
+-----------------------------+----------------+-------------+  
| pan_arguments_test          | helpers        | jsonb       |
+-----------------------------+----------------+-------------+  
| test_bizday                 | public         | boolean     |
+-----------------------------+----------------+-------------+  
| test_checkdigits            | public         | boolean     |
+-----------------------------+----------------+-------------+  
| test_jasper_dynamic_columns | scratch        | record      |
+-----------------------------+----------------+-------------+

Upvotes: 1

Related Questions