Обсуждение: 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$$;
>
>