How to create INSERT logs from SELECTs?

user_14730981 0
Viewed 75

As school work we're supposed to create a table that logs all operations done by users on another table. To be more clear, say I have table1 and logtable, table1 can contain any info (names, ids, job, etc), logtable contains info on who did what, when on table1. Using a function and a trigger I managed to get the INSERT, DELETE and UPDATE operations to be a logged in logtable, but we're also supposed to keep a log of SELECTs. To be more specific about the SELECTs, in a View if you do a SELECT, this is supposed to be logged into logtable via an INSERT, essentially the logtable is supposed to have a new row with information telling that somebody did a SELECT. My problem is that I can't figure out any way to accomplish this as SELECTs can't make use of triggers and in turn can't make use of functions, and rules don't allow for two different operations to take place. The only thing that came close was using query logs, however as the database is the school's and not mine I can't make any use of them.

Here is a rough example of what I'm working with (in reality tstamp has hours minutes and such):

id   operation       hid       tablename       who       tstamp        val_new       val_old
x    INSERT          x         table1          name      YYYY-MM-DD    newValues     previousValues

That works as intended, but what I also need to get to work is this (Note: Whether val_new and old come out as empty or not in this case is not a concern):

id   operation       hid       tablename       who       tstamp        val_new       val_old
x    SELECT          x         table1          name      YYYY-MM-DD    NULL          previousValues

Any and all help is appreciated.

postgresql
1 Answers

Here is an example:

CREATE TABLE public.test (id integer PRIMARY KEY, value integer);

INSERT INTO test VALUES (1,42),(2,13);

CREATE TABLE test_log(id serial primary key, dbuser varchar,datetime timestamp);

-- get_test() inserts username / timestamp into log, then returns all rows
-- of test

CREATE OR REPLACE FUNCTION get_test() RETURNS SETOF test AS '
    INSERT INTO test_log (dbuser,datetime)VALUES(current_user,now());
    SELECT * FROM test;'
language 'sql';

-- now a view returns the full row set of test by instead calling our function 

CREATE VIEW test_v AS SELECT * FROM get_test();


SELECT * FROM test_v;
 id | value 
----+-------
  1 |    42
  2 |    13
(2 rows)

SELECT * FROM test_log;
 id |  dbuser  |          datetime          
----+----------+----------------------------
  1 | postgres | 2020-11-30 12:42:00.188341
(1 row)

If your table has many rows and/or the selects are complex, you don't want to use this view for performance reasons.

user_106678390
Related