Re: Table create time

On Thu, Aug 31, 2017 at 11:26 AM, Achilleas Mantzios <
achill(at)matrix(dot)gatewaynet(dot)com> wrote:

> On 31/08/2017 18:20, Melvin Davidson wrote:
>
> >you could just create an event trigger looking for CREATE TABLE as
> filter_value:
>
> I have tried that. Unfortunately, I have been unable to extract the table
> name from the event because TG_TABLE_NAME is not
> available during an event trigger, albeit perhaps I am missing something?
>
> You need to use ddl_command_end event and then select from
> pg_event_trigger_ddl_commands() . Search for some example how to do this.
>
> That being said, I still believe it is extra work that could easily be
> avoided and should be added to the postgresql catalogs simply to be
> more feature competitive with Oracle & SQL Server, as well as a boost to
> the PostgreSQL community.
>
> On Thu, Aug 31, 2017 at 10:54 AM, Michael Paquier <
> michael(dot)paquier(at)gmail(dot)com> wrote:
>
>> On Thu, Aug 31, 2017 at 10:21 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
>> wrote:
>> > Wolfgang, as David said, a column in pg_class for the creation time of
>> a table does not exist. I long ago requested that feature as it is
>> > in other DB's (Oracle & MS SQL Server), but the main reason that it was
>> not done was that no one was interested in doing it.
>>
>> Is there any need for a column in pg_class for that? You could just
>> create an event trigger looking for CREATE TABLE as filter_value:
>> https://www.postgresql.org/docs/9.6/static/sql-createeventtrigger.html
>> And then have this event trigger just save the timestamp value of
>> now() in a custom table with the name and/or OID of the relation
>> involved.
>> --
>> Michael
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
*>You need to use ddl_command_end event and then select from
pg_event_trigger_ddl_commands()*

*I have, but the result for CREATE TABLE is an error.*

*CREATE OR REPLACE FUNCTION public.fn_notify_ddl() RETURNS event_trigger
AS$BODY$DECLARE obj record;BEGIN RAISE NOTICE 'Type: %', TG_TAG;
RAISE NOTICE 'Command: %', current_query(); RAISE NOTICE 'Table: %',
(pg_event_trigger_ddl_commands()).objid; -- < causes an error? RAISE
NOTICE 'DB Name: %', current_database(); RAISE NOTICE 'DB User: %',
session_user; RAISE NOTICE 'DB Port: %', inet_server_port(); RAISE
NOTICE 'Server Host: %', inet_server_addr(); RAISE NOTICE 'Client Host:
%', inet_client_addr();END;$BODY$ LANGUAGE plpgsql VOLATILE COST
100;ALTER FUNCTION public.fn_notify_ddl() OWNER TO postgres;*

*CREATE TABLE sneaky_pete( id_col varchar(5), col2 varchar(1),
CONSTRAINT sneaky_pete_pk PRIMARY KEY (id_col));*

*ERROR: query "SELECT (pg_event_trigger_ddl_commands()).objid" returned
more than one row*

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.