Forums PostgreSQL.fr

massup

Membre

audit table with integer column

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?

rjuju

Administrateur

Re : audit table with integer column

Are you aware that this is a french speaking board? Also, next time please post your topic in a more suited category. For now I move it to the general technical section.

Something like that should work:

[...] CASE WHEN NEW."categorySignId" IS NULL THEN 'NULL' ELSE NEW."categorySignId" END [...]

---

Julien.
https://rjuju.github.io/

massup

Membre

Re : audit table with integer column

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'

rjuju

Administrateur

Re : audit table with integer column

According to the doc, concat ignores NULL arguments. Try manual concatenation with quote_nullable() the args instead.

---

Julien.
https://rjuju.github.io/

massup

Membre

Re : audit table with integer column

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?