MIME-Version: 1.0
From: Edwin UY <edwin.uy@gmail.com>
Date: Wed, 28 May 2025 03:01:17 +1200
Message-ID: 
 <CA+wokJ_fJphYtOFxd2nHV7P6B9YdhN3=cOcevGHXPd50iUvreQ@mail.gmail.com>
Subject: How to check if a Procedure or FUNCTION EXIST
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006f408906361f5802"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BwokJ_fJphYtOFxd2nHV7P6B9YdhN3%3DcOcevGHXPd50iUvreQ%40mail.gmail.com>
Precedence: bulk

--0000000000006f408906361f5802
Content-Type: text/plain; charset="UTF-8"

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-postgresql

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

--0000000000006f408906361f5802
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><span style=3D"font-family:monospace">Hi,</span></div=
><div><span style=3D"font-family:monospace"><br></span></div><div><span sty=
le=3D"font-family:monospace">Probably a dumb question of sorts.=C2=A0</span=
></div><div><span style=3D"font-family:monospace">I want to check for funct=
ion and procedure if they exist or not including those created by the users=
 as well as system functions</span></div><div><span style=3D"font-family:mo=
nospace"><br></span></div><div><span style=3D"font-family:monospace">Readin=
g thru the following link</span></div><div><span style=3D"font-family:monos=
pace"><br></span></div><div><span style=3D"font-family:monospace"><a href=
=3D"https://stackoverflow.com/questions/34305186/how-to-check-whether-a-sto=
red-procedure-exists-on-postgresql">https://stackoverflow.com/questions/343=
05186/how-to-check-whether-a-stored-procedure-exists-on-postgresql</a></spa=
n></div><div><span style=3D"font-family:monospace"><br></span></div><div><s=
pan style=3D"font-family:monospace">SELECT EXISTS (<br>=C2=A0 =C2=A0 =C2=A0=
 =C2=A0 SELECT *<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM pg_catalog.pg_proc<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 JOIN pg_namespace ON pg_catalog.pg_proc.proname=
space =3D pg_namespace.oid<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE proname =3D=
 &#39;proc_name&#39;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND pg_na=
mespace.nspname =3D &#39;schema_name&#39;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 )<=
/span></div><div><span style=3D"font-family:monospace"><br></span></div><di=
v><span style=3D"font-family:monospace"><a href=3D"https://www.postgresql.o=
rg/docs/current/catalog-pg-proc.html">https://www.postgresql.org/docs/curre=
nt/catalog-pg-proc.html</a></span></div><div><span style=3D"font-family:mon=
ospace"><br></span></div><div><span style=3D"font-family:monospace">Will qu=
erying pg_catalog.pg_proc be enough? Using prokind to check if it is functi=
on or procedure and proowner whether it is a system function or user-define=
d?</span></div><div><span style=3D"font-family:monospace"><br></span></div>=
<div><span style=3D"font-family:monospace">Regards,</span></div><div><span =
style=3D"font-family:monospace">Ed</span></div><div><span style=3D"font-fam=
ily:monospace"><br></span></div></div>

--0000000000006f408906361f5802--