Re: Query to check existence of stored procedure?

then this will not work.

---------- Original Message -----------
From: "Jim C. Nasby" <jnasby(at)pervasive(dot)com>
To: Jim Buttafuoco <jim(at)contactbda(dot)com>
Cc: Alexander Scholz <alexander(dot)scholz1(at)freenet(dot)de>, pgsql-general(at)postgresql(dot)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(dot)scholz1(at)freenet(dot)de>
> > To: pgsql-general(at)postgresql(dot)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(at)pervasive(dot)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 -------