Re: Returning a default value from an INSTEAD Of trigger

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.