Re: Stored FUnctions - Mailing list pgsql-general

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@micropat.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@postgresql.org)

--
Martijn van Oosterhout   <kleptog@svana.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