Thread: Problem with Trigger

Problem with Trigger

From
"Don Mies (NIM)"
Date:

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:

 

  1. 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.
  2. Is there a better way to assure that the input data does not overflow a string column?
  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?
  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.  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

 

 

Re: Problem with Trigger

From
Tom Lane
Date:
"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