public inbox for [email protected]
help / color / mirror / Atom feedHow to check if a Procedure or FUNCTION EXIST
6+ messages / 4 participants
[nested] [flat]
* How to check if a Procedure or FUNCTION EXIST
@ 2025-05-27 15:01 Edwin UY <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Edwin UY @ 2025-05-27 15:01 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
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-postg...
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to check if a Procedure or FUNCTION EXIST
@ 2025-05-27 15:06 David G. Johnston <[email protected]>
parent: Edwin UY <[email protected]>
1 sibling, 2 replies; 6+ messages in thread
From: David G. Johnston @ 2025-05-27 15:06 UTC (permalink / raw)
To: Edwin UY <[email protected]>; +Cc: Pgsql-admin <[email protected]>
On Tue, May 27, 2025 at 8:02 AM Edwin UY <[email protected]> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to check if a Procedure or FUNCTION EXIST
@ 2025-05-27 15:09 David Okeamah <[email protected]>
parent: David G. Johnston <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: David Okeamah @ 2025-05-27 15:09 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; Edwin UY <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Go ahead
DAVID OKEAMAH,DEVELOPER
________________________________
From: David G. Johnston <[email protected]>
Sent: Tuesday, May 27, 2025 4:06:44 PM
To: Edwin UY <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: How to check if a Procedure or FUNCTION EXIST
On Tue, May 27, 2025 at 8:02 AM Edwin UY <[email protected]<mailto:[email protected]>> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to check if a Procedure or FUNCTION EXIST
@ 2025-05-27 21:36 Laurenz Albe <[email protected]>
parent: Edwin UY <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2025-05-27 21:36 UTC (permalink / raw)
To: Edwin UY <[email protected]>; Pgsql-admin <[email protected]>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to check if a Procedure or FUNCTION EXIST
@ 2025-05-28 00:22 Edwin UY <[email protected]>
parent: David G. Johnston <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Edwin UY @ 2025-05-28 00:22 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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 :(
Regards,
Ed
On Wed, May 28, 2025 at 3:07 AM David G. Johnston <
[email protected]> wrote:
> On Tue, May 27, 2025 at 8:02 AM Edwin UY <[email protected]> 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.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to check if a Procedure or FUNCTION EXIST
@ 2025-05-28 00:26 David G. Johnston <[email protected]>
parent: Edwin UY <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2025-05-28 00:26 UTC (permalink / raw)
To: Edwin UY <[email protected]>; +Cc: Pgsql-admin <[email protected]>
On Tuesday, May 27, 2025, Edwin UY <[email protected]> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-05-28 00:26 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-27 15:01 How to check if a Procedure or FUNCTION EXIST Edwin UY <[email protected]>
2025-05-27 15:06 ` David G. Johnston <[email protected]>
2025-05-27 15:09 ` David Okeamah <[email protected]>
2025-05-28 00:22 ` Edwin UY <[email protected]>
2025-05-28 00:26 ` David G. Johnston <[email protected]>
2025-05-27 21:36 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox