Thread: Query to check existence of stored procedure?

Query to check existence of stored procedure?

From

Alexander Scholz

Date:

18 April 2006, 13:57:46

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.

Re: Query to check existence of stored procedure?

From

"Nikolay Samokhvalov"

Date:

18 April 2006, 14:06:49

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

Re: Query to check existence of stored procedure?

From

"A. Kretschmer"

Date:

18 April 2006, 14:14:51

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

Re: Query to check existence of stored procedure?

From

"Jim Buttafuoco"

Date:

18 April 2006, 14:44:43

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

Re: Query to check existence of stored procedure?

From

Alexander Scholz

Date:

20 April 2006, 03:16:14

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.

Re: Query to check existence of stored procedure?

From

"Jim Buttafuoco"

Date:

20 April 2006, 11:56:15

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

Re: Query to check existence of stored procedure?

From

"Jim C. Nasby"

Date:

21 April 2006, 05:51:23

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

Re: Query to check existence of stored procedure?

From

"Jim C. Nasby"

Date:

21 April 2006, 05:55:02

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

Re: Query to check existence of stored procedure?

From

"Jim Buttafuoco"

Date:

21 April 2006, 11:49:11

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

Re: Query to check existence of stored procedure?

From

"Jim Buttafuoco"

Date:

21 April 2006, 11:49:52

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