From: David Johnston <polobo@yahoo.com>
To: 'Russell Keane' <Russell.Keane@inps.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: Updatable view should truncate table fields
Date: Tue, 8 Nov 2011 16:45:59 -0500
Message-ID: <007a01cc9e5f$cddf0a90$699d1fb0$@yahoo.com> (raw)
In-Reply-To: <8D0E5D045E36124A8F1DDDB463D548557CC7AB3B52@mxsvr1.is.inps.co.uk>
References: <8D0E5D045E36124A8F1DDDB463D548557CC7AB3B52@mxsvr1.is.inps.co.uk>
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 field2
FROM 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, Russell.Keane@inps.co.uk
Subject: Re: Updatable view should truncate table fields
In-Reply-To: <007a01cc9e5f$cddf0a90$699d1fb0$@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox