Обсуждение: How to display user-defined functions?
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.
As a wish list, a command like, '\dF' to display functions I have defined,
including those returning opaque would be nice.
---------------------------------------------------
-- Listed by '\df' amongst many other functions
CREATE FUNCTION getTimeStamp() RETURNS timestamp AS '
DECLARE
cur_time timestamp;
BEGIN
cur_time = ''now'';
RETURN cur_time;
END;
' LANGUAGE 'plpgsql';
--------------------------------------
-- Not listed by '\df' or by any other means I can locate
CREATE FUNCTION exon_foreign_keys() RETURNS opaque AS '
DECLARE
zhvt_row zhvt%ROWTYPE;
BEGIN
IF NEW.zhvtID ISNULL THEN
RAISE EXCEPTION ''zhvtID can not be NULL'';
END IF;
SELECT * INTO zhvt_row FROM zhvt WHERE zhvtID = NEW.zhvtID;
IF NOT FOUND THEN
RAISE EXCEPTION ''zhvtID = % is not in TABLE zhvt'' , NEW.zhvtID;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
Thanks,
Mark
--
Mark Dalphin email: mdalphin@amgen.com
Mail Stop: 29-2-A phone: +1-805-447-4951 (work)
One Amgen Center Drive +1-805-375-0680 (home)
Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)
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@ludwig.ucl.ac.uk |
+--------------------------+--------------------------------------+
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. afaik this *should* work: SELECT * FROM pg_proc WHERE procname='your_f_name_here'; I'm not sure of "procname",though also try to display system tables (I think \dS or something) and peek into other suspicious pg_* tables :) [snip] -- CIMPOESU Teodor, Web Programmer (h) @ DIGICOM S.A. Bucharest, Romania @ Internet, site development @ teo@digiro.net, +(401)-330.47.28 official home page ~ http://www.digiro.net/ Internet web page ~ http://internet.digiro.net/
Your functions are stored in the table pg_proc. If you want to display the text of a function, try this: select PROSRC from pg_proc where proname = 'your_function_name'; José Teodor Cimpoesu ha scritto: > 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. > > afaik this *should* work: > SELECT * FROM pg_proc WHERE procname='your_f_name_here'; > I'm not sure of "procname",though > also try to display system tables (I think \dS or something) > and peek into other suspicious pg_* tables :) > [snip] > -- > CIMPOESU Teodor, Web Programmer (h) > @ DIGICOM S.A. Bucharest, Romania > @ Internet, site development > @ teo@digiro.net, +(401)-330.47.28 > > official home page ~ http://www.digiro.net/ > Internet web page ~ http://internet.digiro.net/ > > ************