Query to check existence of stored procedure?

select proname from pg_catalog.pg_proc where proname = 'my_proc_name';

On 4/18/06, Alexander Scholz <alexander.scholz1@freenet.de> wrote:

Hi Newsgroup,

I need a query which can check for the existence of a certain stored
procedure.

(The pendant for MS SQL is

IF EXISTS (SELECT * FROM "sysobjects" WHERE "id" =
object_id(N'"MyTestStoredProcedure"') and OBJECTPROPERTY("id",
N'IsProcedure') = 1)
...
)

Any help would be appreciated! :-)

Thanx in advance,

Alexander.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Best regards,
Nikolay

am 18.04.2006, um 8:26:49 +0200 mailte Alexander Scholz folgendes:

Hi Newsgroup,

I need a query which can check for the existence of a certain stored
procedure.

proname in pg_proc.

select count(*) from pg_proc where proname = 'your_function';

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

don't forget about schema's, you will need to join with pg_namespace.oid and pg_proc.pronamespace

---------- Original Message -----------
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-general@postgresql.org
Sent: Tue, 18 Apr 2006 16:14:25 +0200
Subject: Re: [GENERAL] Query to check existence of stored procedure?

am 18.04.2006, um 8:26:49 +0200 mailte Alexander Scholz folgendes:

Hi Newsgroup,

I need a query which can check for the existence of a certain stored
procedure.

proname in pg_proc.

select count(*) from pg_proc where proname = 'your_function';

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

------- End of Original Message -------

Hi Jim,

select count(*) from pg_proc where proname = 'your_function';

don't forget about schema's, you will need to join with
pg_namespace.oid and pg_proc.pronamespace

your answer looks a little bit cryptic for me being somebody who hasn't
had to dive into the pg_... tables yet. :-)

What do you exactly mean? Could you provide me a complete query for that
job?

Is there anything to consider, if the user performing this query is NOT
the owner of the stored prodcedure? (but he needs this info as well!)

Thank you in advance,

Alexander.

Give this function a try, examples at the end, I used Postgresql 8.1.3

-- s is the schema to look in
-- f is the function name

create or replace function isfunctionavailable(s text,f text)
returns bool
as
$$
declare
ans bool;
begin
select into ans true
from pg_proc p
join pg_namespace n on(p.pronamespace = n.oid)
where proname = f
and nspname = s
group by proname
having count(*) > 0;

return coalesce(ans,false);
end;
$$
language plpgsql
;

select IsFunctionAvailable('public'::text,'isfunctionavailable'::text);
select IsFunctionAvailable('junk'::text,'isfunctionavailable'::text);
select IsFunctionAvailable('public'::text,'junk'::text);

---------- Original Message -----------
From: Alexander Scholz <alexander.scholz1@freenet.de>
To: pgsql-general@postgresql.org
Sent: Tue, 18 Apr 2006 17:08:50 +0200
Subject: Re: [GENERAL] Query to check existence of stored procedure?

Hi Jim,

select count(*) from pg_proc where proname = 'your_function';

don't forget about schema's, you will need to join with
pg_namespace.oid and pg_proc.pronamespace

your answer looks a little bit cryptic for me being somebody who hasn't
had to dive into the pg_... tables yet. :-)

What do you exactly mean? Could you provide me a complete query for that
job?

Is there anything to consider, if the user performing this query is NOT
the owner of the stored prodcedure? (but he needs this info as well!)

Thank you in advance,

Alexander.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

------- End of Original Message -------

And what happens if you have an overloaded function? :)

On Thu, Apr 20, 2006 at 07:55:50AM -0400, Jim Buttafuoco wrote:

Give this function a try, examples at the end, I used Postgresql 8.1.3

-- s is the schema to look in
-- f is the function name

create or replace function isfunctionavailable(s text,f text)
returns bool
as
$$
declare
ans bool;
begin
select into ans true
from pg_proc p
join pg_namespace n on(p.pronamespace = n.oid)
where proname = f
and nspname = s
group by proname
having count(*) > 0;

return coalesce(ans,false);
end;
$$
language plpgsql
;

select IsFunctionAvailable('public'::text,'isfunctionavailable'::text);
select IsFunctionAvailable('junk'::text,'isfunctionavailable'::text);
select IsFunctionAvailable('public'::text,'junk'::text);

---------- Original Message -----------
From: Alexander Scholz <alexander.scholz1@freenet.de>
To: pgsql-general@postgresql.org
Sent: Tue, 18 Apr 2006 17:08:50 +0200
Subject: Re: [GENERAL] Query to check existence of stored procedure?

Hi Jim,

select count(*) from pg_proc where proname = 'your_function';

don't forget about schema's, you will need to join with
pg_namespace.oid and pg_proc.pronamespace

your answer looks a little bit cryptic for me being somebody who hasn't
had to dive into the pg_... tables yet. :-)

What do you exactly mean? Could you provide me a complete query for that
job?

Is there anything to consider, if the user performing this query is NOT
the owner of the stored prodcedure? (but he needs this info as well!)

Thank you in advance,

Alexander.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

------- End of Original Message -------

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

If you're on a more recent version, you can try and select the procname
for a proc and trap the error:

decibel=# select 'abs(bigint)'::regprocedure;
regprocedure
--------------
abs(bigint)
(1 row)

decibel=# select 'abs(text)'::regprocedure;
ERROR: function "abs(text)" does not exist
decibel=#

If you don't care about arguments you can use regproc.

On Tue, Apr 18, 2006 at 08:26:49AM +0200, Alexander Scholz wrote:

Hi Newsgroup,

I need a query which can check for the existence of a certain stored
procedure.

(The pendant for MS SQL is

IF EXISTS (SELECT * FROM "sysobjects" WHERE "id" =
object_id(N'"MyTestStoredProcedure"') and OBJECTPROPERTY("id",
N'IsProcedure') = 1)
...
)

Any help would be appreciated! :-)

Thanx in advance,

Alexander.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

then this will not work.

---------- Original Message -----------
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Jim Buttafuoco <jim@contactbda.com>
Cc: Alexander Scholz <alexander.scholz1@freenet.de>, pgsql-general@postgresql.org
Sent: Fri, 21 Apr 2006 00:51:17 -0500
Subject: Re: [GENERAL] Query to check existence of stored procedure?

And what happens if you have an overloaded function? :)

On Thu, Apr 20, 2006 at 07:55:50AM -0400, Jim Buttafuoco wrote:

Give this function a try, examples at the end, I used Postgresql 8.1.3

-- s is the schema to look in
-- f is the function name

create or replace function isfunctionavailable(s text,f text)
returns bool
as
$$
declare
ans bool;
begin
select into ans true
from pg_proc p
join pg_namespace n on(p.pronamespace = n.oid)
where proname = f
and nspname = s
group by proname
having count(*) > 0;

return coalesce(ans,false);
end;
$$
language plpgsql
;

select IsFunctionAvailable('public'::text,'isfunctionavailable'::text);
select IsFunctionAvailable('junk'::text,'isfunctionavailable'::text);
select IsFunctionAvailable('public'::text,'junk'::text);

---------- Original Message -----------
From: Alexander Scholz <alexander.scholz1@freenet.de>
To: pgsql-general@postgresql.org
Sent: Tue, 18 Apr 2006 17:08:50 +0200
Subject: Re: [GENERAL] Query to check existence of stored procedure?

Hi Jim,

select count(*) from pg_proc where proname = 'your_function';

don't forget about schema's, you will need to join with
pg_namespace.oid and pg_proc.pronamespace

your answer looks a little bit cryptic for me being somebody who hasn't
had to dive into the pg_... tables yet. :-)

What do you exactly mean? Could you provide me a complete query for that
job?

Is there anything to consider, if the user performing this query is NOT
the owner of the stored prodcedure? (but he needs this info as well!)

Thank you in advance,

Alexander.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

------- End of Original Message -------

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

------- End of Original Message -------

nice, this could be put into a plpgsql function with error handling.

---------- Original Message -----------
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Alexander Scholz <alexander.scholz1@freenet.de>
Cc: pgsql-general@postgresql.org
Sent: Fri, 21 Apr 2006 00:54:51 -0500
Subject: Re: [GENERAL] Query to check existence of stored procedure?

If you're on a more recent version, you can try and select the procname
for a proc and trap the error:

decibel=# select 'abs(bigint)'::regprocedure;
regprocedure
--------------
abs(bigint)
(1 row)

decibel=# select 'abs(text)'::regprocedure;
ERROR: function "abs(text)" does not exist
decibel=#

If you don't care about arguments you can use regproc.

On Tue, Apr 18, 2006 at 08:26:49AM +0200, Alexander Scholz wrote:

Hi Newsgroup,

I need a query which can check for the existence of a certain stored
procedure.

(The pendant for MS SQL is

IF EXISTS (SELECT * FROM "sysobjects" WHERE "id" =
object_id(N'"MyTestStoredProcedure"') and OBJECTPROPERTY("id",
N'IsProcedure') = 1)
...
)

Any help would be appreciated! :-)

Thanx in advance,

Alexander.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

------- End of Original Message -------