Re: Query to check existence of stored procedure?

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