Thread: 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
Re: How could I find the last modified procedure in the database?
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
> 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 > >> 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" ... >
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
> >> 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" ...
>
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