Thread: Stored FUnctions
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 --
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 -- 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
Attachment
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).
select * from pg_proc where proname = '' Sincerely, Joshua D. Drake 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 >> >> >> > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
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
Attachment
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).