Обсуждение: Can schemas be ordered regarding their creation time ?
Hi there, I would like to know if it is possible to get the date when the different schemas were created, as I want to write an script to dump only the latest schemas, the latest month created schemas for instance. Thanks in advance, A.A.
Amador Alvarez <aalvarez@d2.com> writes: > I would like to know if it is possible to get the date when the > different schemas were created, as I want to write an script to dump > only the latest schemas, the latest month created schemas for instance. No, sorry, Postgres does not track creation time of database objects. regards, tom lane
Thanks Tom, I will figure out then how to add a newly created schema to the schema-list to be backed up (dumped) but not directly as i expected. Cheers, A.A. On 06/05/2012 05:43 PM, Tom Lane wrote: > Amador Alvarez<aalvarez@d2.com> writes: >> I would like to know if it is possible to get the date when the >> different schemas were created, as I want to write an script to dump >> only the latest schemas, the latest month created schemas for instance. > No, sorry, Postgres does not track creation time of database objects. > > regards, tom lane
Amador Alvarez <aalvarez@d2.com> writes: > Hi there, > I would like to know if it is possible to get the date when the > different schemas were created, as I want to write an script to dump > only the latest schemas, the latest month created schemas for > instance. You could attach the creation date to the schema name or use "COMMENT ON SCHEMA x IS 'y'".
Thanks hary and Matthias, It is a very good idea, however the schema names are meaningful and not allowed to be attached to a date. Regarding the comment solution ("COMMENT ON SCHEMA x IS 'y'"), it sounds great and I tried to run different examples without happy ending as 'y' must be a literal string. I ran commands like these : comment on schema users is select to_char(current_timestamp,'DD Mon YYYY'); comment on schema users is select quote_literal(to_char(current_timestamp,'DD Mon YYYY')); ..... and even simple concatenated strings : comment on schema users is 'a'||'b'; ERROR: syntax error at or near "||" LINE 1: comment on schema users is 'a'||'b'; ... and even with a function returning a text ... : ( Any idea on doing ("COMMENT ON SCHEMA x IS 'y'") as 'y' variable? Thanks again, I appreciate it a lot, A.A. I have been trying a lot of different options to do this : "COMMENT ON SCHEMA x IS 'y'". On 06/06/2012 01:06 AM, hari.fuchs@gmail.com wrote: > Amador Alvarez<aalvarez@d2.com> writes: > >> Hi there, >> I would like to know if it is possible to get the date when the >> different schemas were created, as I want to write an script to dump >> only the latest schemas, the latest month created schemas for >> instance. > You could attach the creation date to the schema name or use > "COMMENT ON SCHEMA x IS 'y'". > >
Amador Alvarez <aalvarez@d2.com> writes: > Any idea on doing ("COMMENT ON SCHEMA x IS 'y'") as 'y' variable? You could use PL/pgSQL's "EXECUTE" for that: DO $$BEGIN EXECUTE 'COMMENT ON SCHEMA myschema IS ''Created ' || current_timestamp || ''''; END$$;
Thanks a lot Hari, very resourceful, you have been very helpful. cheers, A.A. On 06/07/2012 12:47 AM, hari.fuchs@gmail.com wrote: > Amador Alvarez<aalvarez@d2.com> writes: > >> Any idea on doing ("COMMENT ON SCHEMA x IS 'y'") as 'y' variable? > You could use PL/pgSQL's "EXECUTE" for that: > > DO $$BEGIN > EXECUTE 'COMMENT ON SCHEMA myschema IS ''Created ' || > current_timestamp || ''''; > END$$; > >