Обсуждение: How to check if a Procedure or FUNCTION EXIST

Поиск
Список
Период
Сортировка

How to check if a Procedure or FUNCTION EXIST

От
Edwin UY
Дата:
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'
        )


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

Re: How to check if a Procedure or FUNCTION EXIST

От
Laurenz Albe
Дата:
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