Обсуждение: Problem with Trigger
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
"Don Mies (NIM)" <dmies@networksinmotion.com> writes: > What I'm trying to accomplish is to truncate some input strings if they > are larger than the defined column in our database. This cannot work because the value gets put into the tuple --- and hence cast to the defined column type --- before the trigger can ever fire. If you wanted to define the column as just "text", and put 100% reliance on the trigger to enforce the length limit, then it would work. > 3. 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? Probably. I think the standard's focus on "varchar(N)" is a hangover from the days of 80-column punched cards. In almost every modern-day app, whatever value they're using for N is just picked out of the air and has no business-logic justification whatsoever. Unless you can point to a concrete application-driven reason why you need a limit of exactly N, I think you should be using text. > 4. 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. Not going to happen in plpgsql --- it has no real support for run-time-determined column names. You could make it work in one of the other PLs. I still question the need for it at all, though. regards, tom lane