Separating out the standard functions in PostgreSQL

Question

I’ve been using PostgreSQL for many years, and, among other things, I have added a number of my own user defined scalar functions.

Of course, if I knew about it at the time, I should have added my own functions to a separate schema for better management. Instead, my functions are all mixed in with the standard ones.

Is there a way of distinguishing the standard functions so that I can better organise my own functions?

Answer 1

Yes; look at the schema that contains them. If it is pg_catalog, that is a system function. If not, it is a user defined function.

A grey area are functions that belong to an extension. You can only identify them by their membership in an extension. One simple way is to run a pg_dump -s: functions that belong to extension won't be included in that.

Alternatively, here is a query that will list all functions that don't belong t the PostgreSQL system or an extension:

BEGIN; /* for SET LOCAL */
SET LOCAL search_path = ''; /* show all schemas */
SELECT f.oid::regprocedure
FROM pg_proc AS f
WHERE f.pronamespace NOT IN ('pg_catalog'::regnamespace,
 'information_schema'::regnamespace)
 AND NOT EXISTS (SELECT FROM pg_depend AS d
 WHERE d.objid = f.oid AND d.deptype = 'e');
COMMIT; /* reset "search_path" */

Best practice is to define a schema for the database objects you create yourself. Then create all extensions in the public schema, so that their functions end up there. Don't forget to REVOKE the CREATE privilege on the public schema (if you are using v14 or lower).

• Manngo: OK, I’ve looked in pg_catalog and found a ship load of functions. However in the public schema, for example, I can see some of my own functions, but also functions like ceiling(numberid) which I know that I didn’t write. That same function also appears in pg_catalog so I don’t know why it’s also in `public'.
• Laurenz Albe: No idea how you created that function, but it is not part of the system. I have added a query that might help you.
• Manngo: Thanks, that was helpful. I can see now that public shouldn’t have anything to begin with. I created a test database from template0 and it didn’t have any functions in public. That would mean that it’s safe to drop them all and start agin. It also means that I’ve cluttered up my template with some junk.