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 ),
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
CREATE OR REPLACE FUNCTION app1.insert_person_trigger_fn()
RETURNS trigger AS $$
DECLARE uid thing.id%TYPE;
INSERT INTO model.thing
RETURNING model.thing.id into uid;
INSERT INTO model.person
VALUES (uid, NEW.first_name, NEW.last_name);
$$ 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.
On Sun, 1 Mar 2020 at 13:38, David Roper <[hidden email]> wrote:
The answer is simple. In the trigger function you have to change the "into uid" part to "into NEW.id"
|Free forum by Nabble||Edit this page|