Vous n'êtes pas identifié(e).
Pages : 1
Finaly, i'm not sure the problem was do to CONCAT ignoring NULL argument but to the fact that in the CASE WHEN i was returning either an integer 'NEW."categorySignId" or varchar 'NULL'.
Following syntax works perfectly:
INSERT INTO audit (stamp,query) VALUES( now(), CONCAT('INSERT INTO proposition VALUES(',NEW.id,',',quote_nullable(NEW.text),',',CASE WHEN quote_nullable(NEW."categorySignId") IS NULL THEN 'NULL' ELSE NEW."categorySignId" END,');'))
Is there any risk with an insert query which would looks like: INSERT INTO proposition VALUES(1,'Exemple','1');
While third parameter is an integer?
I already tried this syntax but postgres doesn't like:
mydb=# INSERT INTO proposition VALUES(2944,'Example',NULL);
ERROR: invalid input syntax for integer: "NULL"
LINE 1: ...),',',CASE WHEN NEW."categorySignId" IS NULL THEN 'NULL' ELS...
^
QUERY: INSERT INTO audit (stamp,query) VALUES( now(), CONCAT('INSERT INTO proposition VALUES(',NEW.id,',',quote_nullable(NEW.text),',',CASE WHEN NEW."categorySignId" IS NULL THEN 'NULL' ELSE NEW."categorySignId" END,');'))
CONTEXT: PL/pgSQL function process_audit() line 269 at SQL statement
This comes from the apostrophes around 'NULL'
Hell,
I have a table proposition
Column | Type | Modifiers
------------------+---------+-----------------------------------------------------
id | integer | not null default nextval('proposition_id_seq'::regcl
text | text |
categorySignId | integer |
I'm auditing in table audit all modifications made on table proposition via an audit function containing:
ELSIF (TG_TABLE_NAME = 'proposition') THEN
IF (TG_OP = 'DELETE') THEN
INSERT INTO audit (stamp,query) VALUES( now(), CONCAT('DELETE FROM proposition WHERE id=',OLD.id,';'));
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO audit (stamp,query) VALUES( now(), CONCAT('UPDATE proposition SET text=',quote_nullable(NEW.text),',"categorySignId"=',NEW."categorySignId",' WHERE id=',NEW.id,';'));
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO audit (stamp,query) VALUES( now(), CONCAT('INSERT INTO proposition VALUES(',NEW.id,',',quote_nullable(NEW.text),',',NEW."categorySignId",');'));
RETURN NEW;
END IF;
When i insert following record in proposition:
INSERT INTO proposition VALUES(1,'Exemple',NULL);
I obtain following an audit record with following query field:
INSERT INTO proposition VALUES(1,'Exemple',);
This query is not correct, is there a way to obtain NULL string when the integer column is NULL in order to rebuild initial query?
Apologize for this.
I might have missed an update when doing my test, you are right this is working fine.
Thanks for your help.
Let's consider:
-table test containing fields id and description.
-Table audit containing fields id, timestamp, query
-The audit_function:
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');
It works in your example.
But how can i manage it in the case of a variable new.text containing << doesn't >> with no backslash?
quote_nullable(new.text) doesn't return expected result.
Hello,
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.
Hello,
In order to audit all changes made on a database and reapply them when necessary on an other copy of this db, i have created triggers on related tables, an audit table and a function which writes in audit table each time a change occures on other tables.
I'm reconstructing the SQL query when writting it in audit.
The problem is with text strings containing apastrophe. I would like to automaticaly replace single apastrophe by double apastrophe in order to have a correct syntax.
Below example of the syntax i'm using:
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?
Pages : 1
