Updatable view should truncate table fields - Mailing list pgsql-sql

<div class="WordSection1"><p class="MsoNormal">Using PostgreSQL 9.0.<p class="MsoNormal"> <p class="MsoNormal">We have
atable which is not accessible by client code.<p class="MsoNormal">We also have views with rules and triggers to
interceptany insert or update statements and write that data in a slightly different format back to the table.<p
class="MsoNormal"> <pclass="MsoNormal">A particular field in the table is currently 5 chars but recently we have had
update/ insert statements containing more than 5.<p class="MsoNormal">This obviously (and correctly) throws an error.<p
class="MsoNormal"> <pclass="MsoNormal">We can extend the table to accept more than 5 characters but the view must
return5 characters.<p class="MsoNormal">If we try to extend the table to accept, say, 10 characters the view will
display10.<p class="MsoNormal">If I also cast the view field to 5 characters then any insert with more than 5
charactersstill fails.<p class="MsoNormal"> <p class="MsoNormal">Any ideas???<p class="MsoNormal"> <p
class="MsoNormal"> <pclass="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"> <p
class="MsoNormal">Createtable blah_table<p class="MsoNormal">(<p class="MsoNormal">                blah_id int,<p
class="MsoNormal">               fixed_field char(5)<p class="MsoNormal">);<p class="MsoNormal"> <p
class="MsoNormal">Createor replace view blah_view as<p class="MsoNormal">Select<p class="MsoNormal">               
blah_id,<pclass="MsoNormal">                fixed_field<p class="MsoNormal">from blah_table;<p class="MsoNormal"> <p
class="MsoNormal">CREATEOR REPLACE FUNCTION process_blah_insert(blah_view) RETURNS void AS $body$<p
class="MsoNormal">Begin<pclass="MsoNormal"> <p class="MsoNormal">Insert into blah_table<p class="MsoNormal">(<p
class="MsoNormal">               blah_id,<p class="MsoNormal">                fixed_field<p class="MsoNormal">)<p
class="MsoNormal">Select<pclass="MsoNormal">                $1.blah_id,<p class="MsoNormal">               
$1.fixed_field<pclass="MsoNormal">;<p class="MsoNormal">End;<p class="MsoNormal">$body$ language plpgsql;<p
class="MsoNormal"> <pclass="MsoNormal">CREATE OR REPLACE FUNCTION process_blah_update(blah_view) RETURNS void AS
$body$<pclass="MsoNormal">Begin<p class="MsoNormal"> <p class="MsoNormal">Update blah_table<p class="MsoNormal">Set<p
class="MsoNormal">               fixed_field = $1.fixed_field<p class="MsoNormal">where<p
class="MsoNormal">               blah_id = $1.blah_id<p class="MsoNormal">;<p class="MsoNormal">End; <p
class="MsoNormal">$body$language plpgsql;<p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal">create or
replacerule blah__rule_ins as on insert to blah_view<p class="MsoNormal">do instead<p class="MsoNormal">               
SELECTprocess_blah_insert(NEW);<p class="MsoNormal"> <p class="MsoNormal">create or replace rule blah__rule_upd as on
updateto blah_view<p class="MsoNormal">do instead<p class="MsoNormal">                SELECT<p
class="MsoNormal">                               process_blah_update(NEW);<p class="MsoNormal"> <p
class="MsoNormal"> <pclass="MsoNormal">insert into blah_view values (1, '12345');<p class="MsoNormal">insert into
blah_viewvalues (2, '123456'); --This line fails obviously<p class="MsoNormal"> <p class="MsoNormal"> <p
class="MsoNormal"> <pclass="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"> <p
class="MsoNormal"><spanstyle="font-size:10.0pt">Regards,</span><p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif";color:#365F91"> </span><p class="MsoNormalCxSpMiddle"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><b><spanstyle="font-size:10.0pt;font-family:"Times New
Roman","serif";color:black">RussellKeane</span></b><b><span style="font-size:10.0pt;color:black"></span></b><p
class="MsoNormalCxSpMiddle"style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><b><span
style="font-size:10.0pt;color:black">INPS</span></b><pclass="MsoNormal"><span
style="font-size:10.0pt;color:black"> </span><pclass="MsoNormal"><span style="font-size:9.0pt"><a
href="http://www.inps4.co.uk/news/enewsletter/"><spanstyle="color:blue">Subscribe to the Vision
e-newsletter</span></a></span><pclass="MsoNormal"><span style="font-size:9.0pt"><a
href="http://www.inps4.co.uk/my_vision/helpline/support-bulletins"><spanstyle="color:blue">Subscribe to the Helpline
SupportBulletin</span></a></span><p class="MsoNormal"><span style="font-size:9.0pt;color:black"><img border="0"
height="14"id="Picture_x0020_1" src="cid:image003.png@01CC9E5E.26083BD0" width="14" />  </span><span
style="font-size:9.0pt;color:#E36C0A"><ahref="http://www.inps4.co.uk/rss/helplineblog.rss"><span
style="color:#E36C0A">Subscribeto the Helpline Blog RSS Feed</span></a> </span><p class="MsoNormal"> </div><br /><hr
/><fontcolor="Black" face="Arial" size="2">Registered name: In Practice Systems Ltd.<br /> Registered address: The
BreadFactory, 1a Broughton Street, London, SW8 3QJ<br /> Registered Number: 1788577<br /> Registered in England<br />
Visitour Internet Web site at www.inps.co.uk<br /> The information in this internet email is confidential and is
intendedsolely for the addressee. Access, copying or re-use of information in it by anyone else is not authorised. Any
viewsor opinions presented are solely those of the author and do not necessarily represent those of INPS or any of its
affiliates.If you are not the intended recipient please contact is.helpdesk@inps.co.uk<br /><br /></font>