Re: [GENERAL] Table create time - Mailing list pgsql-general
On Thu, Aug 31, 2017 at 11:26 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
On 31/08/2017 18:20, Melvin Davidson wrote:You need to use ddl_command_end event and then select from pg_event_trigger_ddl_commands(>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 notavailable during an event trigger, albeit perhaps I am missing something?) . 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 bemore 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@gmail.com> wrote:On Thu, Aug 31, 2017 at 10:21 PM, Melvin Davidson <melvin6925@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-createeventtr igger.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;
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;
( id_col varchar(5),
col2 varchar(1),
CONSTRAINT sneaky_pete_pk PRIMARY KEY (id_col)
);
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pgsql-general by date: