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