Is there any oracle user_source table equivalent in postgre?

[v***@tcs.com]

Hello,

Can any one help me if oracle like any user_source table is there in
postgre 8.4 (in pg_catalog or information_schema).
Actually we migrated code from Oracle to Postgre. Now i want to search in
how many places( i.e. in functions ) we have used a particular syntax(
e.g. current_date). This can be achieved by using data dictionary view
user_source in oracle.

thanks in advance...

Venkat
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you

[Thomas Kellerer]

Post by v***@tcs.com
Hello,
Can any one help me if oracle like any user_source table is there in
postgre 8.4 (in pg_catalog or information_schema).
Actually we migrated code from Oracle to Postgre. Now i want to search
in how many places( i.e. in functions ) we have used a particular
syntax( e.g. current_date). This can be achieved by using data
dictionary view user_source in oracle.
thanks in advance...

Source code for stored functions is available in pg_catalog.pg_proc.prosrc
Source code for views is available in pg_catalog.pg_views.definition

All catalog views are documented here:
http://www.postgresql.org/docs/current/static/catalogs.html

Thomas

P.S.: it's either Postgres or PostgreSQL but never Postgre ;)

[Vibhor Kumar]

Post by v***@tcs.com
Hello,
Can any one help me if oracle like any user_source table is there in postgre 8.4 (in pg_catalog or information_schema).
Actually we migrated code from Oracle to Postgre. Now i want to search in how many places( i.e. in functions ) we have used a particular syntax( e.g. current_date). This can be achieved by using data dictionary view user_source in oracle.

You can look into Body of Function using:

Use \df+ Funtionname

Or

use pg_proc catalog table.

--Vibhor

[v***@tcs.com]

Thanks Thomas for your reply.

when i am trying -

select * from pg_catalog.pg_proc.prosrc

i am getting error - cross database reference is not implemented. Also i
am not able to see this table under pg_catalog node in pgAdmin tool.
Same thing is happening with pg_catalog.pg_views.definition.

Can you please suggest, am i missing something.



Regards,
Venkat



From:
***@tcs.com
To:
pgsql-***@postgresql.org, pgsql-***@postgresql.org
Date:
03/05/2010 05:11 PM
Subject:
[NOVICE] Is there any oracle user_source table equivalent in postgre?
Sent by:
pgsql-novice-***@postgresql.org




Hello,

Can any one help me if oracle like any user_source table is there in
postgre 8.4 (in pg_catalog or information_schema).
Actually we migrated code from Oracle to Postgre. Now i want to search in
how many places( i.e. in functions ) we have used a particular syntax(
e.g. current_date). This can be achieved by using data dictionary view
user_source in oracle.

thanks in advance...

Venkat
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you




=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you

[Pavel Stehule]

Post by v***@tcs.com
Thanks Thomas for your reply.
when i am trying -
select * from pg_catalog.pg_proc.prosrc

prosrc is column name

do just select prosrc from pg_proc;

regards
Pavel

Post by v***@tcs.com
i am getting error - cross database reference is not implemented. Also i am
not able to see this table under pg_catalog node in pgAdmin tool.
Same thing is happening with pg_catalog.pg_views.definition.
Can you please suggest, am i missing something.
Regards,
Venkat
------------------------------
Hello,
Can any one help me if oracle like any user_source table is there in
postgre 8.4 (in pg_catalog or information_schema).
Actually we migrated code from Oracle to Postgre. Now i want to search in
how many places( i.e. in functions ) we have used a particular syntax( e.g.
current_date). This can be achieved by using data dictionary view
user_source in oracle.
thanks in advance...
Venkat
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you

[Devrim GÜNDÜZ]

Post by v***@tcs.com
when i am trying -
select * from pg_catalog.pg_proc.prosrc

SELECT prosrc FROM pg_catalog.pg_proc;

[v***@tcs.com]

Thanks to you all it worked..






From:
Devrim GÜNDÜZ <***@gunduz.org>
To:
***@tcs.com
Cc:
pgsql-***@postgresql.org, pgsql-***@postgresql.org
Date:
03/05/2010 05:41 PM
Subject:
Re: [NOVICE] Is there any oracle user_source table equivalent in postgre?

Post by v***@tcs.com
when i am trying -
select * from pg_catalog.pg_proc.prosrc

SELECT prosrc FROM pg_catalog.pg_proc;
--
Devrim GÜNDÜZ
PostgreSQL Danýþmaný/Consultant, Red Hat Certified Engineer
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
[attachment "signature.asc" deleted by Venkatrao B/HYD/TCS]

=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you

[Thomas Kellerer]

Post by v***@tcs.com
Thanks Thomas for your reply.
when i am trying -
select * from pg_catalog.pg_proc.prosrc

You have to use:

SELECT prosrc
FROM pg_catalog.pg_proc


Thomas