3

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:

  1. 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 with SELECT *) gets unwieldy;

  2. The returned result doesn't have the SERIAL type product_id updated, so you don't get the expected result for:

    INSERT INTO PurchaseView(product_name, when_bought) 
    VALUES ('foo', NOW())
    RETURNING *;
    
  • SELECT NEW.product_name and so on. Then you don't join, the FROM clause will have only the other table, the join condition goes into WHERE. – dezso Jul 18 '16 at 15:55
  • @dezso That's my point - my actual tables and views are substantially more complicated, so I'm looking to avoid explicitly referring to individual columns (e.g. with NATURAL JOIN). Also not sure how best to deal with LEFT JOINS as in the example I've given. – beldaz Jul 18 '16 at 20:14
  • 2
    NEW is not a table.. It's a PurchaseView%ROWTYPE .. you can't perform table operations on it, but you CAN perform row oprations on it (which is what dezso is suggesting) – Joishi Bodio Jul 18 '16 at 20:31
  • 1
    I cannot confirm now if it works, but you could try NEW.* – dezso Jul 18 '16 at 21:07
  • 2
    @beldaz Yes, it sucks - but that's one of the downsides of using an INSTEAD OF trigger for a VIEW - especially if the VIEW is large. If your VIEW is defined as SELECT * FROM TABLE JOIN TABLE JOIN TABLE .. then you should probably redefine your VIEW so that it doesn't use SELECT *.. – Joishi Bodio Jul 18 '16 at 21:16
2

NEW is a record, not a table. Basics:

Slightly modified setup

CREATE TABLE product (
  product_id serial PRIMARY KEY,
  product_name text UNIQUE NOT NULL  -- must be UNIQUE
);

CREATE TABLE purchase (
  purchase_id serial PRIMARY KEY,
  product_id  int REFERENCES product,
  when_bought date
);

CREATE VIEW purchaseview AS
SELECT pu.purchase_id, pr.product_name, pu.when_bought
FROM   purchase     pu
LEFT   JOIN product pr USING (product_id);

INSERT INTO product(product_name) VALUES ('foo');

product_name has to be UNIQUE, 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:

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase(product_id, when_bought)
   SELECT (SELECT product_id FROM product WHERE product_name = NEW.product_name), NEW.when_bought
   RETURNING purchase_id
   INTO   NEW.purchase_id;  -- generated serial ID for RETURNING - if needed

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER insert_productview_trig
INSTEAD OF INSERT ON purchaseview
FOR EACH ROW EXECUTE PROCEDURE insert_purchaseview_func();

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 generated purchase_id.

If the product is not found (NEW.product_name does not exist in table product), the purchase is still inserted and product_id is NULL. This may or may not be desirable.

2.

To skip the row instead (and possibly raise a WARNING / EXCEPTION):

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase AS pu
            (product_id,     when_bought)
   SELECT pr.product_id, NEW.when_bought
   FROM   product pr
   WHERE  pr.product_name = NEW.product_name
   RETURNING pu.purchase_id
   INTO   NEW.purchase_id;  -- generated serial ID for RETURNING - if needed

   IF NOT FOUND THEN  -- insert was canceled for missing product
      RAISE WARNING 'product_name % not found! Skipping INSERT.', quote_literal(NEW.product_name);
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

This piggybacks NEW columns to SELECT .. FROM product. If the product is found, everything proceeds normally. If not, no row is returned from the SELECT and no INSERT happens. The special PL/pgSQL variable FOUND is only true if the last SQL query processed at least one row.

Could be EXCEPTION instead of WARNING to raise an error and roll back the transaction. But I'd rather declare purchase.product_id NOT NULL and insert unconditionally (query 1 or similar), to the same effect: raises an exception if product_id is NULL. Simpler, cheaper.

3. For multiple lookups

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase AS pu
            (product_id,   when_bought)     -- more columns?
   SELECT pr.product_id, i.when_bought      -- more columns?
   FROM  (SELECT NEW.*) i                   -- see below
   LEFT   JOIN product  pr USING (product_name)
-- LEFT   JOIN tbl2     t2 USING (t2_name)  -- more lookups?
   RETURNING pu.purchase_id                 -- more columns?
   INTO   NEW.purchase_id;                  -- more columns?

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

The LEFT JOINs make the INSERT unconditional again. Use JOIN instead to skip if one is not found.

FROM (SELECT NEW.*) i transforms the record NEW into a derived table with a single row, which can be used like any table in the FROM clause - what you were looking for, initially.

db<>fiddle here

  • Thanks, this nicely removes the CTE. It's been a while since this problem was perplexing me, but I think it deserves to be the new accepted answer. – beldaz Jan 22 at 23:33
3

As suggested in the comments, it looks like the closest I can do to what I want is (fixing up my original approach in the question):

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 purchase_id INTO tmp;
    NEW.purchase_id = tmp.purchase_id;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

This does at least make the RETURNING clause work correctly. It looks like the attributes of NEW must be explicitly declared. The following:

-- Using NEW.* in CTE doesn't work
CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$BODY$
DECLARE
tmp RECORD;
BEGIN
    WITH input  as (
       values (NEW.*)
    ) 
    INSERT INTO Purchase(product_id, when_bought)
    SELECT product_id, when_bought
    FROM input
    LEFT JOIN Product USING (product_name)
    RETURNING purchase_id INTO tmp;
    NEW.purchase_id = tmp.purchase_id;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

results in ERROR: column "product_name" specified in USING clause does not exist in left table when the trigger is fired.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.