To: Hilary Forbes <hforbes@dmr.co.uk>
Cc: Pgsql-sql@postgresql.org
Subject: Re: Where are user defined functions stored? 
In-reply-to: <5.1.0.14.0.20050923171510.0205bc40@mailserver.dmr.co.uk> 
References: <5.1.0.14.0.20050923150802.03484f38@mailserver.dmr.co.uk>
	<5.1.0.14.0.20050923150802.03484f38@mailserver.dmr.co.uk>
	<5.1.0.14.0.20050923171510.0205bc40@mailserver.dmr.co.uk>
Comments: In-reply-to Hilary Forbes <hforbes@dmr.co.uk>
	message dated "Fri, 23 Sep 2005 17:18:22 +0100"
Date: Fri, 23 Sep 2005 12:22:33 -0400
Message-ID: <4679.1127492553@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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 hoping I 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 the 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