Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: How to check if a Procedure or FUNCTION EXIST

Re: How to check if a Procedure or FUNCTION EXIST

From
"David G. Johnston"
Date:
On Tue, May 27, 2025 at 8:02 AM Edwin UY <edwin.uy@gmail.com> wrote:
I want to check for function and procedure if they exist or not including those created by the users as well as system functions

Are you aware that even in the same schema multiple functions and procedures can share the same "name"?

 
proowner whether it is a system function or user-defined?


That seems like a poor test for that property.  A system function is one that exists in the pg_catalog schema, which is the schema the system places all of its objects into.

David J.

Re: How to check if a Procedure or FUNCTION EXIST

From
David Okeamah
Date:
Go ahead

DAVID OKEAMAH,DEVELOPER 
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Tuesday, May 27, 2025 4:06:44 PM
To: Edwin UY <edwin.uy@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: How to check if a Procedure or FUNCTION EXIST
 
On Tue, May 27, 2025 at 8:02 AM Edwin UY <edwin.uy@gmail.com> wrote:
I want to check for function and procedure if they exist or not including those created by the users as well as system functions

Are you aware that even in the same schema multiple functions and procedures can share the same "name"?

 
proowner whether it is a system function or user-defined?


That seems like a poor test for that property.  A system function is one that exists in the pg_catalog schema, which is the schema the system places all of its objects into.

David J.

Re: How to check if a Procedure or FUNCTION EXIST

From
"David G. Johnston"
Date:
On Tuesday, May 27, 2025, Edwin UY <edwin.uy@gmail.com> wrote:
Hi David J

Just thought I will just have to check whether the proowner is one of the users/roles that we've created and if it is so, then it is not a system function.
By system function, I mean something like version or current_user, they are system functions, right? Or am I wrong again :(

I mean, so long as you never use/leave “postgres” (or whatever your bootstrap role name is) as the owner of a user-defined function it can work.  Schema just seems easier though…

David J.