How to check whether a stored procedure exists on PostgreSQL?

Question

I'm pretty new to PostgreSQL and trying to learn PostgreSQL with the knowledge that I have on MS SQL Server & Oracle. I am looking for an equivalent of the following statement that we can use in MS SQL Server to check whether a Stored procedure exists or not, in PostgreSQL where SPName is your stored procedure's name.

SELECT 1 FROM sys.procedures WHERE Name = 'SPName')
SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'dbo.SPName')

Answer 1

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'
        )

If you've not created a specific schema then use public(pg_namespace.nspname = 'public')

---

OR

You can create a custom function to do the task like below:

create or replace function function_exists (sch text,fun text) returns boolean as
$$
begin
EXECUTE  'select pg_get_functiondef('''||sch||'.'||fun||'''::regprocedure)';
return true;
exception when others then 
return false;
end;
$$ language plpgsql

and use :

select function_exists('public','function_name()')

Answer 2

One-liner:

SELECT to_regproc('schema_name.proc_name') IS NOT NULL