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.helpd...@inps.co.uk
<<inline: image003.png>>