public static ListenCommand ListenOnDatabaseEvents()
{
var lc = new ListenCommand();
lc.ProcedureName = $"xa_n_dbEvent";
lc.CommandText = $@"
CREATE OR REPLACE FUNCTION {lc.ProcedureName}() RETURNS event_trigger AS $$
DECLARE
r RECORD;
notification json;
BEGIN
FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
notification= json_build_object(
'action', r.command_tag,
'schema', r.schema_name,
'table', r.object_identity
);
PERFORM pg_notify('{lc.ProcedureName}', notification::text);
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION {lc.ProcedureName}_dropped() RETURNS event_trigger AS $$
DECLARE
r RECORD;
notification json;
action text;
BEGIN
FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
notification= json_build_object(
'action', 'DROP ' || upper(r.object_type),
'schema', r.schema_name,
'table', r.object_identity
);
PERFORM pg_notify('{lc.ProcedureName}', notification::text);
END LOOP;
END;
$$ LANGUAGE plpgsql;
";
return(lc);
}
public static ListenCommand ListenHistoryKeys(string schemaName, string tableName)
{
if (String.IsNullOrWhiteSpace(schemaName))
{
throw new ArgumentNullException(nameof(schemaName));
}
if (String.IsNullOrWhiteSpace(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
var lc = new ListenCommand();
lc.ProcedureName = $"xa_n_historyKeys";
lc.CommandText = $@"
CREATE OR REPLACE FUNCTION ""{schemaName}"".""{lc.ProcedureName}""() RETURNS TRIGGER AS $$
DECLARE
history_schema_table_name text;
history_table_name text;
insert_query text;
history_table_columns text;
primarykeys text[];
return_object json;
cid uuid;
notification json;
BEGIN
history_table_name:= '""' || TG_TABLE_NAME || '#history""';
history_schema_table_name:='""' || TG_TABLE_SCHEMA || '"".' || history_table_name;
history_table_columns:= (SELECT array_to_string(array(SELECT '""' || column_name::text || '""' FROM information_schema.columns WHERE table_schema = TG_TABLE_SCHEMA AND table_name = TG_TABLE_NAME || '#history'), ','));
primarykeys:= ARRAY((SELECT '""' || a.attname || '""' FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) WHERE i.indrelid = history_schema_table_name::regclass AND i.indisprimary));
insert_query := 'INSERT INTO ' || history_schema_table_name || ' (' || history_table_columns || ') VALUES (DEFAULT,''' || TG_OP || ''',DEFAULT,$1.*) RETURNING ""#id""';
IF(TG_OP = 'INSERT') THEN
EXECUTE insert_query USING NEW INTO cid;
END IF;
IF(TG_OP = 'DELETE') THEN
EXECUTE insert_query USING OLD INTO cid;
END IF;
IF(TG_OP = 'UPDATE') THEN
EXECUTE insert_query USING NEW INTO cid;
IF(OLD = NEW) THEN
RETURN NULL;
END IF;
END IF;
return_object:=(SELECT json_build_object('#id',cid));
notification= json_build_object(
'table', TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME,
'action', TG_OP,
'data', return_object);
PERFORM pg_notify('{lc.ProcedureName}', notification::text);
RETURN NEW;
END
$$ LANGUAGE plpgsql;
";
return(lc);
}
public static ListenCommand ListenReferenceKeys(string schemaName, string tableName)
{
if (String.IsNullOrWhiteSpace(schemaName))
{
throw new ArgumentNullException(nameof(schemaName));
}
if (String.IsNullOrWhiteSpace(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
var lc = new ListenCommand();
lc.ProcedureName = $"xa_n_referenceKeys";
lc.CommandText = $@"
CREATE OR REPLACE FUNCTION ""{schemaName}"".""{lc.ProcedureName}""() RETURNS TRIGGER AS $$
DECLARE
js jsonb;
primarykeys text[];
tablename text;
return_object jsonb;
i text;
notification json;
BEGIN
tablename:= '""' || TG_TABLE_SCHEMA || '"".""' || TG_TABLE_NAME || '""';
primarykeys:= ARRAY((SELECT a.attname FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) WHERE i.indrelid = tablename::regclass AND i.indisprimary));
return_object:= '{{}}'::jsonb;
IF(TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
js:= to_jsonb(NEW);
FOREACH i IN ARRAY primarykeys
LOOP
return_object:= (SELECT jsonb_insert(return_object,ARRAY[i] , to_jsonb(js->>i), true));
END LOOP;
END IF;
IF(TG_OP = 'DELETE') THEN
js:= to_jsonb(OLD);
FOREACH i IN ARRAY primarykeys
LOOP
return_object:= (SELECT jsonb_insert(return_object,ARRAY[i] , to_jsonb(js->>i), true));
END LOOP;
END IF;
notification = json_build_object(
'table', TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME,
'action', TG_OP,
'data', return_object);
RAISE NOTICE '%',notification;
PERFORM pg_notify('{lc.ProcedureName}', notification::text);
RETURN NEW;
END
$$ LANGUAGE plpgsql;
";
return(lc);
}
