PostgreSQL: create view everytime a table is updated

Question

I'm new to Postgresql 14; I am trying to create a trigger on a table that creates a view every time the table is modified by INSERT / UPDATE / DELETE. After applying the trigger I am getting this error when trying to update/insert/delete anything in my table:

ERROR: control reached end of trigger procedure without RETURN; CONTEXT: PL/pgSQL function createmyview(); SQL state: 2F005

I am assuming my trigger function is missing something...

This is my trigger function:

CREATE FUNCTION createmyVIEW() RETURNS trigger AS $$
BEGIN
    DROP VIEW IF EXISTS public.myVIEW; 

    CREATE VIEW public.myVIEW AS

    SELECT * FROM data.mytable;
END
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

Applying the trigger:

CREATE TRIGGER syncView AFTER INSERT OR UPDATE OR DELETE ON data.mytable
FOR EACH STATEMENT EXECUTE PROCEDURE createmyView();

Why do you want to re-create a view on every data change? The view will automatically reflect changes in the underlying tables when they are modified. — The Impaler, Feb 07 '22 at 04:24

score 3 · Answer 1 · answered Dec 13 '22 at 15:13

Try Using 'RETURN' in the function, like:

CREATE FUNCTION createmyVIEW() RETURNS trigger AS $$
BEGIN
  DROP VIEW IF EXISTS public.myVIEW; 
  CREATE VIEW public.myVIEW AS
  SELECT * FROM data.mytable;
RETURN NULL;
END
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

PostgreSQL: create view everytime a table is updated

1 Answers1