Re: [GENERAL] How to display user-defined functions?
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 > @ [EMAIL PROTECTED], +(401)-330.47.28 > > official home page ~ http://www.digiro.net/ > Internet web page ~ http://internet.digiro.net/ > >
Re: [GENERAL] How to display user-defined functions?
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 @ [EMAIL PROTECTED], +(401)-330.47.28 official home page ~ http://www.digiro.net/ Internet web page ~ http://internet.digiro.net/
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 /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<>'' 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 | [EMAIL PROTECTED] | +--+--+
[GENERAL] 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: [EMAIL PROTECTED] 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)