Thread: Where are user defined functions stored?

Where are user defined functions stored?

From
Hilary Forbes
Date:
If I write a rule, I can easily see it by doing

select * from pg_rules;

and I also get to see all the rules I have created for my database.  How can I easily get to see the definition of a
userdefined function please?  I've searched through the online manual but failed to find the answer.
 

TAI
Hilary

Hilary Forbes
The DMR Information and Technology Group  (www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330 
DMR is a UK registered trade mark of DMR Limited
**********************************************************



Re: Where are user defined functions stored?

From
Tom Lane
Date:
Hilary Forbes <hforbes@dmr.co.uk> writes:
> How can I easily get to see the definition of a user defined function
> please?

Look in pg_proc.
        regards, tom lane


Re: Where are user defined functions stored?

From
Hilary Forbes
Date:
Tom

Many thanks.  Suppose I now want to know if there are any user defined functions set up in my database?  I was rather
hopingI could do something simple like
 

\df

to get a list of **user** defined functions rather as \dt gives me a list of my tables and not the complete list of all
thesystem tables as well.  Is there a way of achieving this?
 

Hilary

At 10:40 23/09/2005 -0400, you wrote:

>Hilary Forbes <hforbes@dmr.co.uk> writes:
>> How can I easily get to see the definition of a user defined function
>> please?
>
>Look in pg_proc.
>
>                        regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq

Hilary Forbes
The DMR Information and Technology Group  (www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330 
DMR is a UK registered trade mark of DMR Limited
**********************************************************



Re: Where are user defined functions stored?

From
Tom Lane
Date:
Hilary Forbes <hforbes@dmr.co.uk> writes:
> Many thanks.  Suppose I now want to know if there are any user defined functions set up in my database?  I was rather
hopingI could do something simple like
 
> \df
> to get a list of **user** defined functions rather as \dt gives me a list of my tables and not the complete list of
allthe system tables as well.  Is there a way of achieving this?
 

You could do something like\df public.*
although if your functions are scattered through a bunch of different
schemas it'd get a bit tedious.  Or try looking at the catalog for
yourself, eg

select oid::regprocedure, prosrc from pg_proc
where pronamespace != (select oid from pg_namespace                      where nspname = 'pg_catalog');

This essentially implements the rule "system functions are those in
pg_catalog".  You might want to exclude stuff in information_schema
as well.
        regards, tom lane