Re: Cayenne 4.2—Modifying SQL generation to always use COALESCE when updating a certain column

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 <[email protected]> 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