Re: Stored FUnctions

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

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