This is a multi-part message in MIME format.
------_=_NextPart_001_01C8AF09.8B0E09FA
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
I'm currently using PostgreSQL 8.2.7 and having trouble getting a
trigger to work as I think it should.
=20
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.
=20
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)".
=20
I then defined the following function and trigger:
=20
CREATE OR REPLACE FUNCTION string_test()
RETURNS trigger AS
$$
BEGIN
NEW.test_column :=3D substr ( NEW.test_column, 1, 10 );
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
=20
CREATE TRIGGER check_string BEFORE INSERT OR UPDATE ON test_table
FOR EACH ROW EXECUTE PROCEDURE string_test();
=20
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:
=20
test=3D# INSERT INTO test_table VALUES ('short');
INSERT 0 1
test=3D# INSERT INTO test_table VALUES ('a string that is too long');
ERROR: value too long for type character varying(10)
=20
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.
=20
So I have several questions:
=20
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] :=3D substr ( new.TG_ARGV[0], 1, TG_ARGV[1] );". I have
not been able to find any syntax that will make that work.
=20
=20
Don
=20
=20
------_=_NextPart_001_01C8AF09.8B0E09FA
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Font Definitions */
@[EMAIL PROTECTED]
7 4 9 2 2 5 2 4 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
@[EMAIL PROTECTED]
Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
/* List Definitions */
@[EMAIL PROTECTED]
l0
{mso-list-id:1100568514;
mso-list-type:hybrid;
mso-list-template-ids:-182268614 67698703 67698713 67698715 67698703 =
67698713 67698715 67698703 67698713 67698715;}
@[EMAIL PROTECTED]
l0:level1
{mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
-->
</style>
</head>
<body lang=3DEN-US link=3Dblue vlink=3Dpurple>
<div class=3DSection1>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>I’m currently using PostgreSQL 8.2.7 and having
trouble getting a trigger to work as I think it =
should.<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>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.<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>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)”.<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>I then defined the following function and =
trigger:<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'>CREATE OR REPLACE =
FUNCTION
string_test()<o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'> =
RETURNS trigger AS<o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'> =
$$<o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'> &n=
bsp; BEGIN<o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'> &n=
bsp; NEW.test_column :=3D
substr ( NEW.test_column, 1, 10 );<o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'> &n=
bsp; RETURN =
NEW;<o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'> &n=
bsp; END;<o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'> =
$$<o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'> =
LANGUAGE plpgsql;<o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'><o:p> </o:p></span></=
font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCourier><span =
style=3D'font-size:10.0pt;
font-family:Courier'> CREATE TRIGGER =
check_string BEFORE INSERT OR UPDATE
ON test_table<o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'> FOR =
EACH ROW EXECUTE PROCEDURE
string_test();<o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'><o:p> </o:p></span></=
font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>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:<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'>test=3D# INSERT INTO =
test_table
VALUES ('short');<o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'>INSERT 0 =
1<o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'>test=3D# INSERT INTO =
test_table
VALUES ('a string that is too long');<o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DCourier><span
style=3D'font-size:10.0pt;font-family:Courier'>ERROR: value too =
long for type
character varying(10)<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>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.<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>So I have several =
questions:<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<ol style=3D'margin-top:0in' start=3D1 type=3D1>
<li class=3DMsoNormal style=3D'mso-list:l0 level1 lfo1'><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'>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.<o:p></o:p></span></font></li>
<li class=3DMsoNormal style=3D'mso-list:l0 level1 lfo1'><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'>Is there a better way =
to assure
that the input data does not overflow a string =
column?<o:p></o:p></span></font></li>
<li class=3DMsoNormal style=3D'mso-list:l0 level1 lfo1'><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'>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?<o:p></o:p></span></font></li>
<li class=3DMsoNormal style=3D'mso-list:l0 level1 lfo1'><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'>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] :=3D substr ( =
new.TG_ARGV[0],
1, TG_ARGV[1] );”. I have not been able to find any =
syntax that
will make that work.<o:p></o:p></span></font></li>
</ol>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'> <o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Don</span></font><o:p></o:p></p>
<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'> </span><o:p></o:p></font></p>
<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
</body>
</html>
------_=_NextPart_001_01C8AF09.8B0E09FA--
|
