pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCan schemas be ordered regarding their creation time ?
7+ messages / 3 participants
[nested] [flat]
* Can schemas be ordered regarding their creation time ?
@ 2012-06-06 00:22 Amador Alvarez <aalvarez@d2.com>
0 siblings, 2 replies; 7+ messages in thread
From: Amador Alvarez @ 2012-06-06 00:22 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can schemas be ordered regarding their creation time ?
@ 2012-06-06 00:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Amador Alvarez <aalvarez@d2.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2012-06-06 00:43 UTC (permalink / raw)
To: Amador Alvarez <aalvarez@d2.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can schemas be ordered regarding their creation time ?
@ 2012-06-06 00:58 Amador Alvarez <aalvarez@d2.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Amador Alvarez @ 2012-06-06 00:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can schemas be ordered regarding their creation time ?
@ 2012-06-06 08:06 hari.fuchs@gmail.com
parent: Amador Alvarez <aalvarez@d2.com>
1 sibling, 1 reply; 7+ messages in thread
From: hari.fuchs@gmail.com @ 2012-06-06 08:06 UTC (permalink / raw)
To: pgsql-admin
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'".
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can schemas be ordered regarding their creation time ?
@ 2012-06-06 20:44 Amador Alvarez <aalvarez@d2.com>
parent: hari.fuchs@gmail.com
0 siblings, 1 reply; 7+ messages in thread
From: Amador Alvarez @ 2012-06-06 20:44 UTC (permalink / raw)
To: pgsql-admin
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'".
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can schemas be ordered regarding their creation time ?
@ 2012-06-07 07:47 hari.fuchs@gmail.com
parent: Amador Alvarez <aalvarez@d2.com>
0 siblings, 1 reply; 7+ messages in thread
From: hari.fuchs@gmail.com @ 2012-06-07 07:47 UTC (permalink / raw)
To: pgsql-admin
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$$;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can schemas be ordered regarding their creation time ?
@ 2012-06-07 18:29 Amador Alvarez <aalvarez@d2.com>
parent: hari.fuchs@gmail.com
0 siblings, 0 replies; 7+ messages in thread
From: Amador Alvarez @ 2012-06-07 18:29 UTC (permalink / raw)
To: pgsql-admin; hari.fuchs@gmail.com
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$$;
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2012-06-07 18:29 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-06 00:22 Can schemas be ordered regarding their creation time ? Amador Alvarez <aalvarez@d2.com>
2012-06-06 00:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-06-06 00:58 ` Amador Alvarez <aalvarez@d2.com>
2012-06-06 08:06 ` hari.fuchs@gmail.com
2012-06-06 20:44 ` Amador Alvarez <aalvarez@d2.com>
2012-06-07 07:47 ` hari.fuchs@gmail.com
2012-06-07 18:29 ` Amador Alvarez <aalvarez@d2.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox