CODEWITHSUNDEEP
sqlpostgresqlviewmaterialized-viewstriggers
MaciekS
MaciekS

Reputation: 451

postgresql create trigger on materialized view

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();

Upvotes: 3

Views: 7242

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95731

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.

Upvotes: 4

Related Questions