Postgresql: How to escape single quotes in Database trigger?
<p>I created a database trigger to store the row data in an auditing table. 
During the update operation, this trigger takes data from the main table and inserts it to a history table. (history table has columns: date, operation type say Update/Delete, actual row data)
But the trigger fails in some cases because of the quoted text in input data.</p>

<p>How can I escape the quoted text in my trigger? </p>

<pre><code>--My trigger
CREATE OR REPLACE FUNCTION audit.if_modified() RETURNS TRIGGER AS $function$
DECLARE
    temp_row RECORD; -- a temporary variable used on updates/deletes
    v_sql text;
BEGIN
    IF TG_WHEN &lt;&gt; 'AFTER' THEN
        RAISE EXCEPTION 'audit.if_modified() may only run as an AFTER trigger';
    END IF;

v_sql = 'select * from ' || TG_TABLE_NAME::regclass || '_history';
execute v_sql into temp_row;

select now() into temp_row.action_tstamp_tx;
temp_row.action = SUBSTRING(TG_OP,1,1);
IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN
    temp_row.row_data = OLD;
ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN
    temp_row.row_data = OLD;
ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN
    temp_row.row_data = NEW;
ELSE
    RAISE EXCEPTION '[audit.if_modified] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL;
    RETURN NULL;
END IF;

EXECUTE 'INSERT INTO audit.' || TG_TABLE_NAME::regclass || '_history VALUES (''' || 
temp_row.action_tstamp_tx || ''',''' ||
temp_row.action  || ''',''' ||
temp_row.row_data  || ''')'; 

RETURN NULL;
END;
$function$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = audit,public,pg_catalog;
</code></pre>

<p>This works fine for normal use cases but if the varchar data has single quoted text, then it fails to load data into history table.</p>

<pre><code>ERROR:  syntax error at or near "s"
LINE 1: ...VALUES ('2016-02-22 11:44:43.994295-06','U','(6,Tom's,"2016-02...
                                                               ^
QUERY:  INSERT INTO audit.test_history VALUES ('2016-02-22 11:44:43.994295-06','U','(6,Tom's,"2016-02-22 09:49:32.315543")')
CONTEXT:  PL/pgSQL function if_modified() line 30 at EXECUTE
</code></pre>

<p>I am new to Postgresql. I tried with options like</p>

<pre><code>regexp_replace() API
</code></pre>

<p>and</p>

<pre><code>SELECT into temp_row.row_data unnest(('{' || trim((temp_row.row_data)::text, '()') || '}')::text[]);
</code></pre>

<p>etc but I couldn't understand how to loop through the ROWTYPE data and create the correct insert record.</p>

<p>Please share your thoughts on how can I edit my trigger to insert text with single quotes.</p>

<p>Thanks,</p>