How could I find the last modified procedure in the database?

On 11/16/11 8:00 AM, Dhimant Patel wrote:

I have postgres *(PostgreSQL) 9.0.3 running.*
I also created several procedures/functions and now I don't remember
the last procedure I worked on! - I thought I could always get this
from metadata.

Now I'm stuck - couldn't find this details anywhere in catalog tables!

Is there anyway I can get this information?

as far as I know, nothing like that is stored, unless you log DDL
operations, and timestamp your logs, in which case it would be in the
logfiles.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

Dhimant Patel <drp4kri@gmail.com> wrote:

I have postgres (PostgreSQL) 9.0.3 running.
I also created several procedures/functions and now I don't remember the last
procedure I worked on! - I thought I could always get this from metadata.

Now I'm stuck - couldn't find this details anywhere in catalog tables!

Is there anyway I can get this information?

Maybe with this query:

select proname from pg_proc order by oid desc limit 1;

but i'm not really sure ... tias (try it and see)

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

Andreas Kretschmer <akretschmer@spamfence.net> writes:

Dhimant Patel <drp4kri@gmail.com> wrote:

I also created several procedures/functions and now I don't remember the last
procedure I worked on! - I thought I could always get this from metadata.
Now I'm stuck - couldn't find this details anywhere in catalog tables!
Is there anyway I can get this information?

Maybe with this query:
select proname from pg_proc order by oid desc limit 1;
but i'm not really sure ... tias (try it and see)

The OIDs would tell you the creation order, but they don't change during
CREATE OR REPLACE FUNCTION; so depending on what the OP means by "worked
on", this query might not be very useful to him.

I'd try looking to see which row in pg_proc has the latest xmin.
Unfortunately you can't "ORDER BY xmin" ...

regards, tom lane

On Wed, Nov 16, 2011 at 07:02:11PM -0500, Tom Lane wrote:

I'd try looking to see which row in pg_proc has the latest xmin.
Unfortunately you can't "ORDER BY xmin" ...

order by age(xmin) ?

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/

Try this:

select proname,oid, xmin from pg_catalog.pg_proc order by xmin::text desc;

regards

Robert Bernier

Correction, try this:

select proname,oid, xmin from pg_catalog.pg_proc order by xmin::text::int
desc;

regards

Robert Bernier

Andreas Kretschmer <akretschmer(at)spamfence(dot)net> writes:

Dhimant Patel <drp4kri(at)gmail(dot)com> wrote:

I also created several procedures/functions and now I don't remember the

last

On 2011-11-16, Dhimant Patel <drp4kri@gmail.com> wrote:

I have postgres *(PostgreSQL) 9.0.3 running.*
I also created several procedures/functions and now I don't remember the
last procedure I worked on! - I thought I could always get this from
metadata.

Now I'm stuck - couldn't find this details anywhere in catalog tables!

Is there anyway I can get this information?

if you generated any exceptions during your work on the functions you
may find evidence in the database log.

--
⚂⚃ 100% natural