How to check if a Procedure or FUNCTION EXIST

From: Edwin UY <edwin(dot)uy(at)gmail(dot)com>
To: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: How to check if a Procedure or FUNCTION EXIST
Date: 2025-05-27 15:01:17
Message-ID: CA+wokJ_fJphYtOFxd2nHV7P6B9YdhN3=cOcevGHXPd50iUvreQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

Probably a dumb question of sorts.
I want to check for function and procedure if they exist or not including
those created by the users as well as system functions

Reading thru the following link

https://stackoverflow.com/questions/34305186/how-to-check-whether-a-stored-procedure-exists-on-postgresql

SELECT EXISTS (
SELECT *
FROM pg_catalog.pg_proc
JOIN pg_namespace ON pg_catalog.pg_proc.pronamespace =
pg_namespace.oid
WHERE proname = 'proc_name'
AND pg_namespace.nspname = 'schema_name'
)

https://www.postgresql.org/docs/current/catalog-pg-proc.html

Will querying pg_catalog.pg_proc be enough? Using prokind to check if it is
function or procedure and proowner whether it is a system function or
user-defined?

Regards,
Ed

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2025-05-27 15:06:44 Re: How to check if a Procedure or FUNCTION EXIST
Previous Message David G. Johnston 2025-05-27 14:11:16 Re: Tables in public schema