public inbox for [email protected]  

help / color / mirror / Atom feed

From: Sándor Daku <[email protected]>
To: David Roper <[email protected]>
Cc: [email protected]
Subject: Re: Returning a default value from an INSTEAD Of trigger
Date: Sun, 1 Mar 2020 15:01:04 +0100
Message-ID: <CAKyoTgbk7QrFF_mT3oA4R_+-4Kc2dQA1zyqO_Ano7rA9rd3znQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Sun, 1 Mar 2020 at 13:38, David Roper <[email protected]> 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

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Returning a default value from an INSTEAD Of trigger
  In-Reply-To: <CAKyoTgbk7QrFF_mT3oA4R_+-4Kc2dQA1zyqO_Ano7rA9rd3znQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox