Re: 'value too long' and before insert/update trigger

Hi

2017-08-24 7:08 GMT+02:00 Kevin Golding <KGolding(at)axessgroup(dot)com(dot)au>:

> Hi all
> I'm currently migrating a legacy Informix 4gl application to run on
> PostgreSQL (v9.5.8)
>
> There are errors occurring because sometimes the application tries to
> insert/update values longer than the definition of the database column.
> The error message is eg. "value too long for type character(20)".
>
> The behaviour under Informix is to silently truncate the saved value, but
> with PostgreSQL it crashes our application program. Obviously the short
> answer is to modify the application so that it does not attempt to save
> values that are too long, but with 1000+ programs and approx. 8000
> insert/update statements to review this will take a lot of time.
>
> I was hoping to emulate the Informix behaviour by creating triggers to run
> before each insert/update that would check the length of the values being
> saved and truncate if too long. However I'm still getting the error
> messages. Presumably the length validation is being done before the trigger
> is run. Is there some way this could be changed so the trigger happens
> first?
>
> I've seen examples that involve changing the char type columns to text,
> but this changes the semantics of string comparisons with regard to
> trailing spaces, and I'm concerned that this might cause subtle and hard to
> find problems within the application.
>
> Thanks
> Kevin
>

The most simple way is using custom type with similar behave like Informix
- I did it with port from MySQL

http://okbob.blogspot.cz/2009/08/mysql-functions-for-postgresql.html

you can use google translator - the article is in Czech language - but can
be translated

Regards

Pavel

>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>