Re: [GENERAL] How to display user-defined functions?

At 9:41 am -0700 31/8/99, Mark Dalphin wrote:
>Hi,
>
>Is there a way to display user-defined functions? For example, if I define a
>function using PL/pgsql, what tables do I query and in what way to learn that
>the function exisits and further, what its defintion is? I can find functions
>that return known types, eg getTimeStamp, below, shows up with '\df', however
>functions defined returning type "opaque" do not show up, eq,
>"exon_foreign_keys", below.

I think you need to query the catalog tables for such answer.

There quite a lot of info on catalog tables in the user manual but I find
one of the most useful resources is a list of example SQL queries that
exploit them which can be found in the tutorial section of the PG sources
(at least for PG6.4.0). try looking for
<PG-source>/src/tutorial/syscat.source

from the syscat.source file:

--
-- lists the name, number of arguments and the return type of all user-defined
-- C functions
--
SELECT p.proname, p.pronargs, t.typname
FROM pg_proc p, pg_language l, pg_type t
WHERE p.prolang = l.oid
and p.prorettype = t.oid
and l.lanname = 'c'
ORDER BY proname;

Now to get a \dF like SELECT, based losely on the above, I got:

SELECT p.proname, p.pronargs, t.typname, p.prosrc
FROM pg_proc p, pg_language l, pg_type t
WHERE p.prolang = l.oid
and p.prorettype = t.oid
and l.lanname in ('c','plpgsql')
and p.proowner='1234'
-- replace with appropriate user_id in previous line
-- or perhaps with p.proowner<>'1111' to select all non-superuser
-- defined functions.
UNION
SELECT p.proname, p.pronargs, 'opaque', p.prosrc
FROM pg_proc p, pg_language l
WHERE p.prolang = l.oid
and p.prorettype = 0 -- opaque functions are listed as having return
type oid 0
and l.lanname in ('c','plpgsql')
and p.proowner='1234' -- as above
ORDER BY proname;

Now I'm sure there's a better way of writing/implementing this query (I
cobbled together v. fast) but it should give you the general idea.

>As a wish list, a command like, '\dF' to display functions I have defined,
>including those returning opaque would be nice.

I must say I'm a big fan of the \d? set of psql 'tools'; \dF get my vote as
an additional one (but should it show source code?).

Also, there was talk as long while back (circa PG 6.3.2) to have add
functionality to the \d? 'functions' (I really don't know what to call
them) such that something like:

\df int4float would pull out \df info but only for function int4float
\da stddev would pull out \da info but only for aggregate stddev

(perhaps even using ~'stddev' instead of ='stddev' if you catch my drift).

Don't know if that ever made it (didn't in PG 6.4.0)

HTH, (& happy exon trapping),

Stuart.

+--------------------------+--------------------------------------+
| Stuart C. G. Rison | Ludwig Institute for Cancer Research |
+--------------------------+ 91 Riding House Street |
| N.B. new phone code!! | London, W1P 8BT |
| Tel. +44 (0)207 878 4041 | UNITED KINGDOM |
| Fax. +44 (0)207 878 4040 | stuart(at)ludwig(dot)ucl(dot)ac(dot)uk |
+--------------------------+--------------------------------------+