Обсуждение: How to check if a Procedure or FUNCTION EXIST
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
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'
)
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'
)
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
On Wed, 2025-05-28 at 03:01 +1200, Edwin UY wrote:
> 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
>
> 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?
That would simply be
SELECT pronamespace::regprocedure AS "schema",
oid::regprocedure
FROM pg_proc
WHERE proname = 'whatever';
Yours,
Laurenz Albe