Skip site navigation (1) Skip section navigation (2)

Re: Problem with Trigger

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Don Mies (NIM)" <dmies(at)networksinmotion(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Problem with Trigger
Date: 2008-05-06 01:08:25
Message-ID: 5944.1210036105@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"Don Mies (NIM)" <dmies(at)networksinmotion(dot)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

In response to

pgsql-novice by date

Next:From: Wright, GeorgeDate: 2008-05-06 14:23:55
Subject: function exception
Previous:From: Don Mies (NIM)Date: 2008-05-05 23:41:29
Subject: Problem with Trigger

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group