Updatable view should truncate table fields - Mailing list 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

Subscribe to the Helpline Support Bulletin

  Subscribe to the Helpline Blog RSS Feed