pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdatable view should truncate table fields
3+ messages / 3 participants
[nested] [flat]
* Updatable view should truncate table fields
@ 2011-11-08 21:34 Russell Keane <Russell.Keane@inps.co.uk>
0 siblings, 2 replies; 3+ messages in thread
From: Russell Keane @ 2011-11-08 21:34 UTC (permalink / raw)
To: pgsql-sql
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.
Any ideas???
Create table blah_table
(
blah_id int,
fixed_field char(5)
);
Create or replace view blah_view as
Select
blah_id,
fixed_field
from blah_table;
CREATE OR REPLACE FUNCTION process_blah_insert(blah_view) RETURNS void AS $body$
Begin
Insert into blah_table
(
blah_id,
fixed_field
)
Select
$1.blah_id,
$1.fixed_field
;
End;
$body$ language plpgsql;
CREATE OR REPLACE FUNCTION process_blah_update(blah_view) RETURNS void AS $body$
Begin
Update blah_table
Set
fixed_field = $1.fixed_field
where
blah_id = $1.blah_id
;
End;
$body$ language plpgsql;
create or replace rule blah__rule_ins as on insert to blah_view
do instead
SELECT process_blah_insert(NEW);
create or replace rule blah__rule_upd as on update to blah_view
do instead
SELECT
process_blah_update(NEW);
insert into blah_view values (1, '12345');
insert into blah_view values (2, '123456'); --This line fails obviously
Regards,
Russell Keane
INPS
Subscribe to the Vision e-newsletter<http://www.inps4.co.uk/news/enewsletter/;
Subscribe to the Helpline Support Bulletin<http://www.inps4.co.uk/my_vision/helpline/support-bulletins;
[cid:image003.png@01CC9E5E.26083BD0] Subscribe to the Helpline Blog RSS Feed<http://www.inps4.co.uk/rss/helplineblog.rss;
________________________________
Registered name: In Practice Systems Ltd.
Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3QJ
Registered Number: 1788577
Registered in England
Visit our Internet Web site at www.inps.co.uk
The information in this internet email is confidential and is intended solely for the addressee. Access, copying or re-use of information in it by anyone else is not authorised. Any views or 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
Attachments:
[image/png] image003.png (816B, ../../8D0E5D045E36124A8F1DDDB463D548557CC7AB3B52@mxsvr1.is.inps.co.uk/3-image003.png)
download | view image
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Updatable view should truncate table fields
@ 2011-11-08 21:45 David Johnston <polobo@yahoo.com>
parent: Russell Keane <Russell.Keane@inps.co.uk>
1 sibling, 0 replies; 3+ messages in thread
From: David Johnston @ 2011-11-08 21:45 UTC (permalink / raw)
To: 'Russell Keane' <Russell.Keane@inps.co.uk>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Updatable view should truncate table fields
@ 2011-11-12 10:09 Jasen Betts <jasen@xnet.co.nz>
parent: Russell Keane <Russell.Keane@inps.co.uk>
1 sibling, 0 replies; 3+ messages in thread
From: Jasen Betts @ 2011-11-12 10:09 UTC (permalink / raw)
To: pgsql-sql
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
--
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-11-12 10:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-08 21:34 Updatable view should truncate table fields Russell Keane <Russell.Keane@inps.co.uk>
2011-11-08 21:45 ` David Johnston <polobo@yahoo.com>
2011-11-12 10:09 ` Jasen Betts <jasen@xnet.co.nz>
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