PostgreSQL – How to Divide Log Files for Specific DB Queries

There must be some kind of misunderstanding. tableoid is a system column that's available for any regular table in any version of Postgres since at least v7.3. Per documentation:

Every table has several system columns that are implicitly defined by the system.

tableoid is one of them. The solution I provided under the questions you refer to is also suggested in the manual here. And it works. I have been using it for years in various Postgres versions.

Either you are not using Postgres, or template is not a table. Is it a VIEW maybe? If you define a VIEW like:

CREATE VIEW template AS
SELECT * FROM some_table;

Then system columns are not included by default.

What do you get for

SELECT relnamespace, relname, relkind
FROM   pg_class
WHERE  relname = 'template';

Is relkind 'r', 'v' or something else?
Or do you get multiple rows? Then consider this:

• Why does a PostgreSQL SELECT query return different results when a schema name is specified?

Ok, after some research, here's the answers I found.

Q1: Is it possible to detect the user that executed the query in MySQL?

It is possible to detect with which user by using: select user();

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_user

Beware: In triggers and events current_user() returns the user who defined the object (in SP and views unless if defined with the SQL SECURITY INVOKER). You really need to use the user() function to return the invoker in those contexts.

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_current-user

Q2: Is it possible to fire more than one action (INSERT, UPDATE, DELETE) on the same trigger?

Most unfortunately, at the current time (2016/02/01), it is not possible to fire two actions on the same time in MySQL. The best we can do is to regroup the common coding within a stored procedure.

The following procedure is one possible way to work around this problem. It expects in input:

1. callingTable: The name of the table for which we want to record the query (must be in the white list).
2. action: Either 'DELETE', 'INSERT' or 'UPDATE'.

3. fieldsValues: a string containing a valid pair of field/value in XML. By example: <field>ID</field><value>1616</value>

   CREATE PROCEDURE `dataLinkRecorder` (
   IN callingTable VARCHAR (25),
   IN action ENUM('DELETE', 'INSERT', 'UPDATE'),
   IN fieldsValues TEXT
   )
   BEGIN
   DECLARE callingUser VARCHAR(25) DEFAULT '';
   DECLARE fieldCount SMALLINT UNSIGNED DEFAULT 0;
   DECLARE fieldCCount SMALLINT UNSIGNED DEFAULT 0;
   DECLARE valueCount SMALLINT UNSIGNED DEFAULT 0;
   DECLARE valueCCount SMALLINT UNSIGNED DEFAULT 0;
   
   IF (TRIM(fieldsValues) <> '') THEN
   
       /* the calling table must be one in the white list */
       SELECT CASE 
           WHEN UPPER(callingTable) IN (
               'CATEGORY',
               'CUSTOMER',
               'MAKE',
               'MODEL',
               'PRODUCT',
               'VEHICLE'
           )
           THEN TRIM(UPPER(callingTable))
           ELSE '' 
           END AS myTable
           INTO callingTable;
   
        SELECT TRIM(
        UPPER(
           IFNULL(USER(), 'CLIENT')
        )
        ) AS myUser
   INTO callingUser;
   
   /* do not record modifications from tier software */
   IF (callingTable <> '' AND INSTR(callingUser, 'CLIENT') < 1) THEN
   
       SELECT IFNULL(
           ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '<field>', ''))) / 7)
       , 0) AS fCount,
       IFNULL(
           ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '</field>', ''))) / 8)
       , 0) AS fCCount,
       IFNULL(
           ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '<value>', ''))) / 7)
       , 0) AS vCount,
       IFNULL(
           ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '</value>', ''))) / 8)
       , 0) AS vCCount
       INTO fieldCount, fieldCCount, valueCount, valueCCount;
   
       /* validate the field/value XML pair */
       IF (fieldCount > 0 AND
           valueCount > 0 AND
           fieldCount = fieldCCount AND
           valueCount = valueCCount AND
           fieldCount = valueCCount
       ) THEN
   
           INSERT INTO `pc_datalink` 
               (`table_name`, `query_xml`) 
           VALUES(
               callingTable,
               CONCAT(
                   '<query>',
                       '<table>', callingTable, '</table>',
                       '<action>', action, '</action>',
                       fieldsValues,
                   '</query>'
               )
           );
       END IF;
   END IF;
   END IF;
   END //
   

Will insert values in the following table:

CREATE TABLE `pc_datalink` (
    `id` int(10) UNSIGNED NOT NULL,
    `table_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `query_xml` text COLLATE utf8_unicode_ci NOT NULL,
    `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `AK_DATE_ADDED` (`date_added`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

By example, a very simple trigger like this one:

CREATE TRIGGER `dataLinkDeleteVehicle` AFTER DELETE ON `pc_vehicle`
FOR EACH ROW
BEGIN
IF (OLD.original_id <> '') THEN

    CALL dataLinkRecorder(
        'VEHICLE',
        'DELETE',
        CONCAT(
            '<field>ID</field><value>',
            OLD.original_id,
            '</value>'
        )
    );
END IF; 
END //

Will generate a line in the table like this one:

Don't hesitate to ask for details if you still have questions,

Good success everyone