group several columns together in materialized view

Question

For statistical reasons, I need to group several ids in a single column of a materialized view. This should form a list of ids from other tables with an info. Further processing will be done on this view. The goal is to save seconds of processing by first generating this view instead of querying all the tables (about 10) one by one.

But I don't understand if I can create a new column name with view or only select one in existing table.

Answer 1

There are two ways to specify the name of a view column:

1. Do it with a column list:

   CREATE VIEW myview (colname1, colname2) AS
   SELECT appa.appa_id,
          font.font_id
   FROM appa JOIN font ON ...;
   

2. Do it with aliases in the defining query:

   CREATE VIEW myview AS
   SELECT appa.appa_id AS colname1,
          font.font_id AS colname2
   FROM appa JOIN font ON ...;