I'm having trouble getting an INSTEAD OF trigger to work correctly, and I think I've misunderstood how to use NEW. Consider the following simplified scenario:
CREATE TABLE Product (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR
);
CREATE TABLE Purchase (
purchase_id SERIAL PRIMARY KEY,
product_id INT REFERENCES Product,
when_bought DATE
);
CREATE VIEW PurchaseView AS
SELECT purchase_id, product_name, when_bought
FROM Purchase LEFT JOIN Product USING (product_id);
I'd like to be able to create INSTEAD OF
triggers to allow me to insert directly into PurchaseView
, e.g.:
INSERT INTO Product(product_name) VALUES ('foo');
INSERT INTO PurchaseView(product_name, when_bought) VALUES ('foo', NOW());
What I had in mind was something along the lines of:
CREATE OR REPLACE FUNCTION insert_purchaseview_func()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO Purchase(product_id, when_bought)
SELECT product_id, when_bought
FROM NEW
LEFT JOIN Product USING (product_name)
RETURNING * INTO NEW;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER insert_productview_trig
INSTEAD OF INSERT
ON PurchaseView
FOR EACH ROW
EXECUTE PROCEDURE insert_purchaseview_func();
However the above trigger function gives errors (relation "new" does not exist
) when executed. I know I can write queries that explicitly use attributes from NEW
in the WHERE
and SELECT
clauses, but sometimes it would be convenient to be able to include NEW
in a join. Is there a way to do this?
Current (unsatisfactory) solution
The closest I can get to what I want is
CREATE OR REPLACE FUNCTION insert_purchaseview_func()
RETURNS trigger AS
$BODY$
DECLARE
tmp RECORD;
BEGIN
WITH input (product_name, when_bought) as (
values (NEW.product_name, NEW.when_bought)
)
INSERT INTO Purchase(product_id, when_bought)
SELECT product_id, when_bought
FROM input
LEFT JOIN Product USING (product_name)
RETURNING * INTO tmp;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
This is a little unsatisfactory for several reasons:
-
I need to explicitly write all the attributes of
NEW
in CTE WITH query, which for large views (especially those whose attributes are automatically determined withSELECT *
) gets unwieldy; -
The returned result doesn't have the
SERIAL
typeproduct_id
updated, so you don't get the expected result for:INSERT INTO PurchaseView(product_name, when_bought) VALUES ('foo', NOW()) RETURNING *;
Best Answer
NEW
is a record, not a table. Basics:Slightly modified setup
product_name
has to beUNIQUE
, or the lookup on this column could find multiple rows, which would lead to all kinds of confusion.1. Simple solution
For your simple example, only looking up the single column
product_id
, a lowly correlated subquery is simplest and fastest:No additional variables. No CTE (would only add cost and noise). Columns from
NEW
are spelled out once only (your point 1).The appended
RETURNING purchase_id INTO NEW.purchase_id
takes care of your point 2: Now, the returned row includes the newly generatedpurchase_id
.If the product is not found (
NEW.product_name
does not exist in tableproduct
), the purchase is still inserted andproduct_id
isNULL
. This may or may not be desirable.2.
To skip the row instead (and possibly raise a
WARNING
/EXCEPTION
):This piggybacks
NEW
columns toSELECT .. FROM product
. If the product is found, everything proceeds normally. If not, no row is returned from theSELECT
and noINSERT
happens. The special PL/pgSQL variableFOUND
is only true if the last SQL query processed at least one row.Could be
EXCEPTION
instead ofWARNING
to raise an error and roll back the transaction. But I'd rather declarepurchase.product_id NOT NULL
and insert unconditionally (query 1 or similar), to the same effect: raises an exception ifproduct_id
isNULL
. Simpler, cheaper.3. For multiple lookups
The
LEFT JOIN
s make theINSERT
unconditional again. UseJOIN
instead to skip if one is not found.FROM (SELECT NEW.*) i
transforms the recordNEW
into a derived table with a single row, which can be used like any table in theFROM
clause - what you were looking for, initially.db<>fiddle here