How to create multiple views from a table column with a PostgreSQL trigger?

January 4, 2023

I work on a PostgreSQL database.

I’d like to use a loop to create multiples views from a table column.

Let’s say I have this table mylist :

id  type  
1   Fish
2   Bird
3   Fish
4   Fish
5   Bird

I use that query to get all the distinct values from the type column:

SELECT DISTINCT type from mylist;

It returns :

type
Fish
Bird

How could I use these two values in order to create automatically the following views (here I have only two values but in reality it’s possible to get more than 50 distinct values):

mylist_fish
mylist_bird

I know that I should use a trigger but don’t see how to create the views according to the values returned by my query.

>Solution :

The trick is to dynamically build and run all the creation statements.

You can do this at the command line, with the \gexec parameter.

SELECT
  FORMAT('CREATE OR REPLACE VIEW my_table_view_%s AS SELECT * FROM myList WHERE type = %L',type,type)
FROM (SELECT DISTINCT type FROM mylist) sub;\gexec

Run it first without the ending \gexec to see what will be executed. Using your sample, it would be:

CREATE OR REPLACE VIEW my_table_view_Fish AS SELECT * FROM myList WHERE type = 'Fish'
CREATE OR REPLACE VIEW my_table_view_Bird AS SELECT * FROM myList WHERE type = 'Bird'

If you really need to put that inside a trigger or inside a function (though I personally wouldn’t blindly create or recreate views on every table update), you can instead use execute. The example below uses an anonymous do block, you can move its content into your trigger.

DO $$
BEGIN
  EXECUTE FORMAT('CREATE OR REPLACE VIEW my_table_view_%s AS SELECT * FROM myList WHERE type = %L',type,type)
  FROM (SELECT DISTINCT type FROM mylist) sub;
END $$;