Problem with Trigger

From: "Don Mies (NIM)" <dmies(at)networksinmotion(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Problem with Trigger
Date: 2008-05-05 23:41:29
Message-ID: 3329C5B4110B6F429B69A8D70AC74D0303A9AF54@exchange1.NIMONE.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2008-05-06 01:08:25 Re: Problem with Trigger
Previous Message John Gunther 2008-05-05 18:10:35 Re: why am I told "subquery must return only one column" (SELECTing values to be used as function arguments)