postgresql create trigger on materialized view

Question

I would like to have a field (produkt) in one table that references a view (owoce).

-- see definition of 'owoce' below
FOREIGN KEY (produkt) REFERENCES owoce(nazwa) ON DELETE RESTRICT

It's forbidden in PostgreSQL (9.3), so I decided to create materialized view instead (it's probably even better - faster- in this case). It's said that materialized views can be used as ordinary tables.

My previously used ordinary view had a trigger that modified underlying table (see below), but when I change the view to materialized I got an error:

'komplety' is not a table nor view

In Oracle it's possible to create a trigger on materialized views (Materialized view with trigger?), and what about PostgreSQL 9.3? Maybe is there any other possibility to make a reference field to view? I'd prefer to use standar view in this case.

Sorry for mixing languages in my code, but my GUI directly uses table names.

CREATE MATERIALIZED VIEW komplety AS
    SELECT nazwa, klient
    FROM produkty
    WHERE komplet = true;

CREATE OR REPLACE FUNCTION update_view_komplety()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $function$
   BEGIN
      IF TG_OP = 'INSERT' THEN
        INSERT INTO produkty(komplet,nazwa,klient,jm) VALUES(true,NEW.nazwa,NEW.klient,'kpl.');
        RETURN NEW;
      ELSIF TG_OP = 'UPDATE' THEN
       UPDATE produkty SET nazwa=NEW.nazwa, klient=NEW.klient WHERE nazwa=OLD.nazwa AND klient=OLD.klient AND komplet=true;
       RETURN NEW;
      ELSIF TG_OP = 'DELETE' THEN
       DELETE FROM produkty WHERE nazwa=OLD.nazwa AND klient=OLD.klient AND komplet=true;
       RETURN NULL;
      END IF;
      RETURN NEW;
    END;
$function$;

CREATE TRIGGER update_view_komplety_trig
    INSTEAD OF INSERT OR UPDATE OR DELETE ON
      komplety FOR EACH ROW EXECUTE PROCEDURE update_view_komplety();

Mike Sherrill 'Cat Recall' · Accepted Answer · 2014-06-20 15:34:53Z

4

I didn't find any documentation that says, "You can't create a trigger on a materialized view."

Nevertheless, it appears that a materialized view isn't a view in the sense that you need it to be. For example, trying to drop a materialized view raises this error.

ERROR:  "komplety" is not a view
HINT:  Use DROP MATERIALIZED VIEW to remove a materialized view.

I think this lines up with what the current docs say about CREATE TRIGGER.

The name (optionally schema-qualified) of the table or view the trigger is for.

Note that it doesn't say "table or view or materialized view".

You can write a trigger for a view that does the work of the foreign key constraint you'd prefer to use.

answered Jun 20, 2014 at 15:34

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MaciekS Over a year ago

Thank you. I used trigger and an ordinary view. I don't know why I was so focused on the constraints, that I forgot about the most obvious solution. I spent hours walking around...

Collectives™ on Stack Overflow

postgresql create trigger on materialized view

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related