Re: Stored FUnctions

Well, using the -E option to psql, \df expands to:

SELECT CASE WHEN p.proretset THEN 'setof ' ELSE '' END ||
pg_catalog.format_type(p.prorettype, NULL) as "Result data type",
n.nspname as "Schema",
p.proname as "Name",
pg_catalog.oidvectortypes(p.proargtypes) as "Argument data types"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
AND NOT p.proisagg
AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 2, 3, 1, 4;

and \df+ expands to

SELECT CASE WHEN p.proretset THEN 'setof ' ELSE '' END ||
pg_catalog.format_type(p.prorettype, NULL) as "Result data type",
n.nspname as "Schema",
p.proname as "Name",
pg_catalog.oidvectortypes(p.proargtypes) as "Argument data types",
u.usename as "Owner",
l.lanname as "Language",
p.prosrc as "Source code",
pg_catalog.obj_description(p.oid, 'pg_proc') as "Description"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = p.proowner
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
AND NOT p.proisagg
AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 2, 3, 1, 4;

So it looks like pg_proc might be your best bet.

Hope this helps,

On Fri, Dec 05, 2003 at 05:59:11PM -0500, Rich Hall wrote:
> Thanks for the psql response.
>
> But, I seldom use psql. I find PG Manager much more useful. Even PG
> Admin was better.
>
> I am looking to write some stored functions to query the database to
> automatically create the repotrs I now generate by hand.
> So I was trying to find my Stored Functions in the database so I could
> query them.
>
> Rick
>
>
> Martijn van Oosterhout wrote:
>
> >On Fri, Dec 05, 2003 at 02:22:26PM -0500, Rich Hall wrote:
> >
> >
> >>Can somebody tell me how Stored Functions are kept in the database? I've
> >>gone through the catalog several times. I've found many interesting
> >>things but NOT the 300-odd functions I've created.
> >>
> >>Rick
> >>
> >>
> >
> >\df
> >
> >
> >
>
> --
> ------------------------------------------------------------------------
> A Message from MicroPatent® LLC
>
> MicroPatent now offers searchable .PDF patents! Save time and improve your
> workflow efficiencies with these convenient, easy-to-review documents. For
> more information, go to
> http://www.micropat.com/0/searchable_pdf/searchable_pdf.html.
>
> Richard Hall
> Database Programmer
> MicroPatent LLC
> 250 Dodge Avenue
> East Haven, CT 06512
> T: <Phone Number>, x 3321
> F: <Fax Number>
> S: <Toll Free Number>
> rhall(at)micropat(dot)com
> www.micropat.com
>
> MicroPatent is an Information Holdings Inc. company (NYSE: IHI).
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato