[SQL] BEFORE UPDATE Triggers
PostgreSQL Version: 7.2.3 Procedural Language: PL/pgSQL I have a table which contains a field for the user who last modified the record. Whenever a row in this table is updated, I want to have an UPDATE trigger do the following things: 1) Ensure the UPDATE query supplied a value for the action_user column 2) Log the record to an audit table so I can retrieve a change log Part 2 was trivial, however it seemed natural that if I had the following conditional in the trigger function: IF NEW.action_user ISNULL THEN ... I could raise an exception if that field was not supplied. (which would be the case if the function were triggered on an INSERT) Unfortunately it seems this is not the case. The NEW record contains values representing both the values explicitly provided with the UPDATE as well as the existing values which were not stipulated in the query. Is there any clever way around this limitation? It isn't the end of the world if I cannot verify this constraint in postgres, however it would have made it easier to ensure no one is making mistakes. Oh, and I am aware of the current_user variable. In my case this is useless as I don't care about the user at the database layer but rather at the application layer. Thanks in advance, cva ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Could not convert UTF-8 to ISO8859-1
I've noticed a difference in behavior between 7.2 and 7.3 with regards
to character recoding and I'm a little perplexed about how to work
around.
I have a database in LATIN-1 that is accessed read-write by a Java app.
Naturally, the Java code keeps all of its strings in UTF8 so when I
prepare a sql statement, someone is recoding these characters to
LATIN-1 for me.
In 7.2, if the Unicode string contained a character that wasn't valid
in the database encoding (LATIN-1) either pgsql or the jdbc driver (I'm
not really sure which) would silently convert these characters to
question marks.
In 7.3, the same string will throw a "Could not convert UTF-8 to
ISO8859-1" error.
I can work around this by doing the following hack in Java:
String s = "some unicode string";
byte[] tmp = s.getBytes("latin1");
s = new String(tmp, 0, tmp.length, "latin1");
But I'm sure there is a better way to do this.
Any suggestions?
cva
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
