Re: Table create time

Melvin Davidson <melvin6925@xxxxxxxxx> · Thu, 31 Aug 2017 11:46:24 -0400

On Thu, Aug 31, 2017 at 11:26 AM, Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> 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.paquier@xxxxxxxxx>
          wrote:

          On Thu, Aug 31, 2017 at 10:21 PM, Melvin Davidson
              <melvin6925@xxxxxxxxx>
              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. 