Обсуждение: Query to check existence of stored procedure?
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.
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 -------