Thanks John, not a bad idea. I'd prefer it if the current method ports though (since I'd have to create triggers for something like 200 columns). But if that isn't possible we might consider this.
- hugi > On 8 Sep 2020, at 20:33, John Huss <johnth...@gmail.com> wrote: > > Since you are already at the DB level (outside of cayenne) making this > change, I would just use a BEFORE UPDATE row-level trigger in Postgres. You > can change the value there. > > Something like this would work: > > CREATE OR REPLACE FUNCTION company_default() > > RETURNS trigger AS $$ > > BEGIN > > NEW.company = coalesce(NEW.company, <some default>); > > RETURN NEW; > > END; > > $$ LANGUAGE plpgsql; > > CREATE TRIGGER mytable_update > BEFORE UPDATE ON MyTable > FOR EACH ROW > WHEN (NEW.company IS NULL) > EXECUTE FUNCTION company_default(); > > > On Tue, Sep 8, 2020 at 3:13 PM Hugi Thordarson <h...@karlmenn.is> wrote: > >> Working with old DB designs really results in the weirdest questions… >> >> So… I've been working around a design problem in a customer DB by using my >> own BatchTranslatorFactory. The functionality was that if a column is >> called "company", every update wraps the column's new value in a coalesce >> function to ensure that it's never set to null (for… reasons). This has >> worked great as a workaround for our problem. >> >> However, SQL generation in Cayenne 4.2 is all new so my current solution ( >> https://gist.github.com/hugith/a33a20fc7da7fd8f709f59ce3a30a96a < >> https://gist.github.com/hugith/a33a20fc7da7fd8f709f59ce3a30a96a> ) >> doesn't really port. >> Before I start considering migration to 4.2, is this possible to do there? >> Or should I just bite the bullet and start fixing up that bloody DB before >> upgrading? >> >> Cheers, >> - hugi
