public inbox for [email protected]
help / color / mirror / Atom feedReturning a default value from an INSTEAD Of trigger
2+ messages / 2 participants
[nested] [flat]
* Returning a default value from an INSTEAD Of trigger
@ 2020-03-01 11:25 David Roper <[email protected]>
2020-03-01 14:01 ` Re: Returning a default value from an INSTEAD Of trigger Sándor Daku <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: David Roper @ 2020-03-01 11:25 UTC (permalink / raw)
To: [email protected]
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Returning a default value from an INSTEAD Of trigger
2020-03-01 11:25 Returning a default value from an INSTEAD Of trigger David Roper <[email protected]>
@ 2020-03-01 14:01 ` Sándor Daku <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Sándor Daku @ 2020-03-01 14:01 UTC (permalink / raw)
To: David Roper <[email protected]>; +Cc: [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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-03-01 14:01 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-01 11:25 Returning a default value from an INSTEAD Of trigger David Roper <[email protected]>
2020-03-01 14:01 ` Sándor Daku <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox