On Thu, Aug 31, 2017 at 11:46 AM, Melvin Davidson <melvin6...@gmail.com>
wrote:
>
>
> 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.
>
*Wolfgang,*
*Kudos to Álvaro Herrera! Thanks to his slide presentation Capturing DDL
Events
<https://pgday.ru/files/papers/22/pgday.2015.alvaro.herrera.capturing-ddl.pdf>*
*I was able to figure out how to trap and log table creates. See the
attached log_tbl_cre8.sql for my solution*
*that I am passing on to help you and others.*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
CREATE SEQUENCE public.log_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE public.log_seq
OWNER TO postgres;
DROP TABLE public.tbl_create_log
CREATE TABLE public.tbl_create_log
(
log_id bigint NOT NULL DEFAULT nextval('log_seq'::regclass),
log_table_schema name NOT NULL,
log_table_name name NOT NULL,
log_table_cre8_time timestamp without time zone NOT NULL DEFAULT
clock_timestamp(),
CONSTRAINT tbl_create_log_pk PRIMARY KEY (log_table_schema, log_table_name)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.tbl_create_log
OWNER TO postgres;
-- Function: public.fn_notify_ddl()
-- DROP FUNCTION public.fn_notify_ddl();
CREATE OR REPLACE FUNCTION public.fn_notify_ddl()
RETURNS event_trigger AS
$BODY$
DECLARE
obj record;
BEGIN
FOR obj IN SELECT *
FROM pg_event_trigger_ddl_commands() LOOP
IF obj.command_tag = 'CREATE TABLE' THEN
RAISE INFO 'we got a % event for object "%"', obj.command_tag,
obj.object_identity;
INSERT INTO tbl_create_log
(log_table_schema, log_table_name)
SELECT n.nspname, c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.oid = obj.objid
AND c.relkind = 'r';
END IF;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.fn_notify_ddl()
OWNER TO postgres;
CREATE EVENT TRIGGER tg_event_audit_all ON ddl command end
EXECUTE PROCEDURE public.fn_notify_ddl();
-- Now test the event & trigger function
CREATE TABLE public.sneaky_pete
(
pk_col character varying(5) NOT NULL,
col2 character varying(10),
CONSTRAINT sneaky_pete_pk PRIMARY KEY (pk_col)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.sneaky_pete
OWNER TO postgres;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
