I’m currently using PostgreSQL 8.2.7 and having trouble getting a trigger to work as I think it should.
What I’m trying to accomplish is to truncate some input strings if they are larger than the defined column in our database. For a number of reasons, this will be difficult to do in the code that is calling PostgreSQL so it is desirable to do via a trigger in the database itself. Truncating the data is acceptable for the fields in question.
For testing purposes I defined a new database called “test” that contains a table called “test_table”. That table contains a single column called “test_column” that is defined as “varchar(10)”.
I then defined the following function and trigger:
CREATE OR REPLACE FUNCTION string_test()
RETURNS trigger AS
$$
BEGIN
NEW.test_column := substr ( NEW.test_column, 1, 10 );
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER check_string BEFORE INSERT OR UPDATE ON test_table
FOR EACH ROW EXECUTE PROCEDURE string_test();
The trigger works fine if the input string is less than or equal to the column size (10 bytes) but if the input string is larger, the trigger never fires:
test=# INSERT INTO test_table VALUES ('short');
INSERT 0 1
test=# INSERT INTO test_table VALUES ('a string that is too long');
ERROR: value too long for type character varying(10)
I have put a “notice” command in the function to verify that the function does not get called in the second case but it does get called in the first.
So I have several questions:
- Why doesn’t the above trigger and function work? It acts as though the database performs the validity checks on the input data BEFORE it calls the trigger function.
- Is there a better way to assure that the input data does not overflow a string column?
- Since the columns that I need to do this to are all somewhat controlled (i.e. They will never be extremely large, I just don’t know exactly how large.) would it be reasonable to just redefine them as “varchar” or “text” with no upper limit?
- If I could make the above code work, it would be highly desireable to write only 1 function that could be called from multiple triggers. However, when I tried to change the code to accept a column name and length as input arguments, I got an error saying that ERROR: record "new" has no field "TG_ARGV[0]" on the line that reads: “new.TG_ARGV[0] := substr ( new.TG_ARGV[0], 1, TG_ARGV[1] );”. I have not been able to find any syntax that will make that work.
Don