Обсуждение: Updatable view should truncate table fields

<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> 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Russell Keane
Sent: Tuesday, November 08, 2011 4:34 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Updatable view should truncate table fields

Using PostgreSQL 9.0.

We have a table which is not accessible by client code.
We also have views with rules and triggers to intercept any insert or update
statements and write that data in a slightly different format back to the
table.

A particular field in the table is currently 5 chars but recently we have
had update / insert statements containing more than 5.
This obviously (and correctly) throws an error.

We can extend the table to accept more than 5 characters but the view must
return 5 characters.
If we try to extend the table to accept, say, 10 characters the view will
display 10.
If I also cast the view field to 5 characters then any insert with more than
5 characters still fails.

-----------------------------------------------------

Haven't used updatable VIEWs yet but couldn't you either define the VIEW as:

CREATE VIEW .... AS (SELECT  field1, field2::varchar(5) AS field2FROM table
);

Or, alternatively, define the INSERT/UPDATE functions to perform the
truncation upon inserting into the table?

Does the INSERT itself throw the error or is one of your functions raising
the error when it goes to insert the supplied value into the table?

It is generally bad form to modify user data for storage so either  you
truly have a length limitation that you need to restrict upon data entry (in
which case everything is working as expected) or you should allow any length
of data to be input and, in cases where the output medium has length
restrictions, you can ad-hoc limit the display length of whatever data was
provided.

David J.

On 2011-11-08, Russell Keane <Russell.Keane@inps.co.uk> wrote:
>
> We can extend the table to accept more than 5 characters but the view must =
> return 5 characters.
> If we try to extend the table to accept, say, 10 characters the view will d=
> isplay 10.
> If I also cast the view field to 5 characters then any insert with more tha=
> n 5 characters still fails.
>
> Any ideas???

re-load the view and functions, they are sill defined with the char(5)
column (you'll probably yneed to drop them all (but not the table) first).

plpgsql functions are partially compiled at the time they are defined 
subsequent modifictions to the datatypes in their definition will
cause errors until they are re-defined 

--