Second: once corrected, everything works here:
test=# create table audit (stamp timestamp with time zone, query text);
test=# create table test (id int, description text);
test=# CREATE TRIGGER test_audit
AFTER INSERT OR UPDATE OR DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE audit_function();
test=# CREATE OR REPLACE FUNCTION audit_function() RETURNS TRIGGER AS
$audit$
BEGIN
--
-- Create a row audit to reflect the operation performed on test,
--
IF (TG_TABLE_NAME = 'test') THEN
IF (TG_OP = 'DELETE') THEN
INSERT INTO audit (stamp,query) VALUES(now(), CONCAT('DELETE FROM test WHERE id=',OLD.id,';'));
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO audit (stamp,query) VALUES( now(), CONCAT('UPDATE test SET id=', NEW.id, ',description=', quote_literal(NEW.description)';'));
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO audit (stamp,query) VALUES( now(), CONCAT('INSERT INTO test VALUES(',NEW.id,',',quote_literal(NEW.description),');'));
RETURN NEW;
END IF;
END IF;
RETURN NULL;
END;
$audit$
LANGUAGE plpgsql;
test=# select * from audit ;
stamp | query
-------------------------------+------------------------------------
2016-07-20 15:21:29.036159+02 | INSERT INTO test VALUES(1,'toto');
(1 ligne)
test=# insert into test values (1,'toto');
test=# insert into test values (2,'Here''s the example');
INSERT 0 1
test=# select * from audit ;
stamp | query
-------------------------------+---------------------------------------------------
2016-07-20 15:21:29.036159+02 | INSERT INTO test VALUES(1,'toto');
2016-07-20 15:21:53.053199+02 | INSERT INTO test VALUES(2,'Here''s the example');
(2 lignes)
So I think you have another problem in your code.
]]>CREATE OR REPLACE FUNCTION audit_function() RETURNS TRIGGER AS $audit$
BEGIN
--
-- Create a row audit to reflect the operation performed on test,
--
IF (TG_TABLE_NAME = 'test') THEN
IF (TG_OP = 'DELETE') THEN
INSERT INTO audit (stamp,query) VALUES(now(), CONCAT('DELETE FROM test WHERE id=',OLD.id,';'));
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO audit (stamp,query) VALUES( now(), CONCAT('UPDATE test SET id=', NEW.id, ',description=', quote_literal(NEW.description)';'));
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO audit (stamp,query) VALUES( now(), CONCAT('INSERT INTO test VALUES(',NEW.id,',',quote_literal(NEW.description),');'));
RETURN NEW;
END IF;
RETURN NULL;
END;
$audit$ LANGUAGE plpgsql;
-Trigger
CREATE TRIGGER test_audit
AFTER INSERT OR UPDATE OR DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE audit_function();
If i insert record id=1, description=Here's the example (INSERT INTO test (id,description) VALUES (1,'Here''s the example');)
I will get in audit table a record with query=INSERT INTO test (stamp,query) VALUES (yyyymmdd-hhmmss,'Here's the example');
select E'doesn\'t';
?column?
----------
doesn't
select quote_nullable(E'doesn\'t');
quote_nullable
----------------
'doesn''t'
Something else must be wrong. Please provide a complete test case.
]]>I tried with quote_nullable and quote_literal but it doesn't produces expected result.
For example i have a variable new.text containing txt : doesn't.
quote_nullable(new.text) returns 'doesn't' while i would have expected to get 'doesn''t'
I obtain same result with quote_literal.
I think what you are looking for is quote_nullable: https://www.postgresql.org/docs/current … tring.html
This will protect everything in you strings. Because there is a lot of room for SQL injections in what you are doing…
]]>CREATE OR REPLACE FUNCTION process_audit() RETURNS TRIGGER AS $audit$
BEGIN
--
-- Create a row in auditv2 to reflect the operation performed on tablename,
-- make use of the special variable TG_OP to work out the operation.
--
IF (TG_TABLE_NAME = 'tablename') THEN
IF (TG_OP = 'DELETE') THEN
INSERT INTO auditv2 (stamp,query) VALUES(now(), CONCAT('DELETE FROM tablename WHERE id=',OLD.id,';'));
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO auditv2 (stamp,query) VALUES( now(), CONCAT('UPDATE tablename SET "typeId"=', NEW."typeId", ',subgroup=''', NEW.subgroup, ''',name=''', NEW.name, ''',"imageName"=''', NEW."imageName", ''' WHERE id=', NEW.id,';'));
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO auditv2 (stamp,query) VALUES( now(), CONCAT('INSERT INTO tablename VALUES(',NEW.id,',''', NEW."typeId",''',''',NEW.subgroup,''',''',NEW.name,''',''',NEW."imageName",''');'));
RETURN NEW;
If name field contains for example "It's the example" i would like to replace it by "It''s the example".
I tried with REPLACE command ( REPLACE (NEW.name, "'", "''") but when inserting a record SQL tells me column "'" doesn't exist!
Any idea?
]]>