Skip to content Skip to sidebar Skip to footer
Home / Database Trigger / Plpgsql / Postgresql / Sql

How To Save A Data With Comma In Character Varying That Passes Through A Trigger?

Post a Comment
I have a field of type character varying but I get an error when trying to save a data that contains decimal. I want to save that data without problem. This is my trigger: CREATE T

Solution 1:

You can use format() to make creating a dynamic SQL query much easier as it will automatically deal with identifiers and literals correctly. One thing that people usually overlook is that you can expand a single record expression to all its columns using (...).* - this also works for NEW and OLD record variables in a trigger, e.g. select (new).*

You can also pass variables to a dynamic SQL with the using keyword of the execute statement. There is no need to convert the record back and forth between a record and a text representation.

Using that possibility your trigger function can be simplified to:

DECLARE 
  l_sql text;
BEGIN
    IF TG_TABLE_SCHEMA ='public'THEN
      newtable := TG_TABLE_NAME ||'_actividad';
    ELSE
      newtable := TG_TABLE_SCHEMA ||'_'|| TG_TABLE_NAME ||'_actividad';
    END IF;

    PERFORM creartablaactividad(TG_TABLE_SCHEMA, TG_TABLE_NAME);
    l_sql :='INSERT INTO actividad.%I  SELECT current_user, current_timestamp, %L, ($1).*';

    IF TG_OP ='DELETE'THENexecute format(l_sql, newtable, 'D') usingOLD;
      RETURNOLD;
    ELSE-- covers UPDATE and INSERTexecute format(l_sql, newtable, 'U') usingNEW;
      RETURNNEW;
    END IF;

    RETURNNULL; -- result is ignored since this is an AFTER triggerEND;

Using placeholders like %I and %L also makes it possible to define the actual SQL only once and re-use it. Those "parameters" are replaced by the format() function (which preserves the $1)

Note the use of ($1).* inside the SQL string. That will make the execute statement expand the record parameter $1 to all its columns. The record itself is passed "natively" with the USING keyword.

The use of INSERT without a target column list (insert into some_table ... instead of insert into some_table (col1, col2, ...) ...) is a pretty fragile thing to do. If the source and the target don't match the insert can fail quite easily. .

If you don't run massive reporting on the audit tables (where having explicit column names would be much more efficient) you might want to think of a more generic audit trigger using a JSON or HSTORE column to store the whole record. There are several ready-made audit triggers available:

Post a Comment for "How To Save A Data With Comma In Character Varying That Passes Through A Trigger?"