Stored FUnctions

[Rich Hall]

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

--


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Martijn van Oosterhout]

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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/0PpYY5Twig3Ge+YRAml5AKDL+DZ584GfNR+OTpEGYc/ZR5fgsQCcDj/2
NjC03GEeK7Dbe5YW6gU2bsA=
=FU84
-----END PGP SIGNATURE-----

[Rich Hall]

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...hable_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>
rh***@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 ma*******@postgresql.org)

[Joshua D. Drake]

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.
[phone] - [e-mail] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Martijn van Oosterhout]

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...hable_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>
rh***@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 ma*******@postgresql.org)

--
Martijn van Oosterhout <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/0RNZY5Twig3Ge+YRAjNOAJ49qjznxcy0MWF9/sUPVYYNb9arIgCfWDM8
/DNQWsYSGBbVYPuOXu0iijc=
=p/jg
-----END PGP SIGNATURE-----

[Rich Hall]

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...hable_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>
rh***@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 ma*******@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...hable_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>
rh***@micropat.com
www.micropat.com

MicroPatent is an Information Holdings Inc. company (NYSE: IHI).

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org