Vous n'êtes pas identifié(e).
- Contributions : Récentes | Sans réponse
Pages : 1
#1 Re : Général » audit table with integer column » 25/07/2016 17:52:05
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?
#2 Re : Général » audit table with integer column » 22/07/2016 17:05:36
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'
#3 Général » audit table with integer column » 22/07/2016 10:28:15
- massup
- Réponses : 4
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?
#4 Re : PL/pgSQL » Automic replacement in SQL query » 21/07/2016 09:21:43
Apologize for this.
I might have missed an update when doing my test, you are right this is working fine.
Thanks for your help.
#5 Re : PL/pgSQL » Automic replacement in SQL query » 20/07/2016 15:05:22
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');
#6 Re : PL/pgSQL » Automic replacement in SQL query » 20/07/2016 09:21:01
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.
#7 Re : PL/pgSQL » Automic replacement in SQL query » 20/07/2016 08:25:25
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.
#8 PL/pgSQL » Automic replacement in SQL query » 18/07/2016 16:22:39
- massup
- Réponses : 9
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