On Thu, Aug 31, 2017 at 11:26 AM, Achilleas Mantzios < ach...@matrix.gatewaynet.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.paqu...@gmail.com> wrote: > >> On Thu, Aug 31, 2017 at 10:21 PM, Melvin Davidson <melvin6...@gmail.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.
