Re: Stored FUnctions - Mailing list pgsql-general

Thats most of what I needed. I shouls be able to suss out the rest.

Thanks

Rick


Martijn van Oosterhout wrote:

>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)
>>
>>
>
>
>

--
------------------------------------------------------------------------
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).