Re: Returning a default value from an INSTEAD Of trigger

On Sun, 1 Mar 2020 at 13:38, David Roper <david(dot)roper(at)me(dot)com> wrote:

> I’d appreciate some help with a problem I’m grappling with.
>
> I am building a generic data model that will underly a number of
> applications. In the generic model there will be entities for PERSON and
> ORGANISATION and various (named) associations between them and other
> entities. The concept of a person being employed by an organisation for a
> period might be represented by an association between a PERSON and an
> ASSOCIATION in the role of “employer/ employee”. The associative entity
> could also contain dates of employment, their role etc.
>
> Applications will access the generic model through their own schema,
> populated with views across the underlying model that represent the
> “business entities” the application will manipulate. An application might
> want to deal with current employees only; it could have an “EMPLOYEE”
> business entity (view) that reduces the many-to-many “employer/employee”
> association (essentially a person’s employment history) to a foreign key to
> the ORGANISATION. All interaction between the application and the data
> model will be through the views in the application’s schema. This includes
> INSERTs, which implies using INSTEAD OF triggers (please correct me if
> RULES would be better; my investigations indicate not, but I don’t really
> understand why).
>
> Continuing, I don’t think it’s practical to use natural identifiers as
> keys, so the generic model uses surrogate keys. This could be a sequence,
> but I’m leaning heavily towards UUIDs. I also don’t think it's a good idea
> for applications to generate keys since there can be no long term guarantee
> that every application will do so correctly. I would therefore like to use
> the uuid_generate_v4() function to generate keys on INSERT and return the
> key in the response to the application. That brings me to my problem: how
> can I do that with an INSTEAD OF trigger function?
>
> To take a trivialised example:
>
> CREATE TABLE model.thing (
> id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
> created TIMESTAMPTZ DEFAULT now(),
> created_by TEXT DEFAULT current_user
> );
>
> CREATE TABLE model.person (
> id UUID PRIMARY KEY REFERENCES model.thing( id ),
> first_name TEXT,
> last_name TEXT NOT NULL
> );
>
> CREATE VIEW app1.person AS
> SELECT t.id, p.first_name, p.last_name
> FROM model.thing t, model.person p
> WHERE t.id = p.id;
>
> CREATE OR REPLACE FUNCTION app1.insert_person_trigger_fn()
> RETURNS trigger AS $$
> DECLARE uid thing.id%TYPE;
> BEGIN
> INSERT INTO model.thing
> DEFAULT VALUES
> RETURNING model.thing.id into uid;
> INSERT INTO model.person
> VALUES (uid, NEW.first_name, NEW.last_name);
> RETURN NEW;
> END
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER insert_person_trig
> INSTEAD OF INSERT ON app1.person
> FOR EACH ROW EXECUTE FUNCTION app1.insert_person_trigger_fn();
>
> INSERT INTO app1.person (first_name, last_name) VALUES (‘John’, ’Smith’);
>
> The INSERT works fine, as expected creating a new row in both the
> model.thing and model.person tables. The problem I’m having is getting at
> the newly created model.thing.id so I can return it to the application.
> RETURN uid gives an error, and RETURN NEW doesn’t seem to return anything!
>
> Any help gratefully received, including suggestions as to better ways.
>
>
Hi David,

The answer is simple. In the trigger function you have to change the "into
uid" part to "into NEW.id"

Regards,
Sándor