How to create a view from an other one?

Question

In postgreSQL, I created a table my_table:

DROP SCHEMA IF EXISTS roipoussiere cascade;
CREATE SCHEMA roipoussiere;

CREATE TABLE roipoussiere.my_table (
  id SERIAL PRIMARY KEY,
  x smallint,
  y smallint);

INSERT INTO roipoussiere.my_table(x, y) VALUES (42, 42);
-- [etc.]

... from which I created views view_a and view_b, that have both the same columns (but different content):

DROP VIEW IF EXISTS roipoussiere.view_a CASCADE;
CREATE VIEW roipoussiere.view_a AS SELECT
  concat_ws('view_a_', x, '_', y) AS foo,
  'Hello' AS bar,
  x,
  y
FROM roipoussiere.my_table;

DROP VIEW IF EXISTS roipoussiere.view_b CASCADE;
CREATE VIEW roipoussiere.view_b AS SELECT
  concat_ws('view_b_', x, '_', y) AS foo,
  'Hello' AS bar,
  x,
  y
FROM roipoussiere.my_table;

... then I created the view my_view, union of view_a and view_b :

DROP VIEW IF EXISTS roipoussiere.my_view CASCADE;
CREATE VIEW roipoussiere.my_view AS
  SELECT * FROM roipoussiere.view_a UNION ALL
  SELECT * FROM roipoussiere.view_b;

But view_a and view_b have a lot of content in common, just some columns are differents. So I would like to avoid redundancy and create view_a, then create view_b from view_a (ie, without creating column bar twice, which is identical for all views).

Note: it is a simplified example, in practice:

• there are 4 views, not 2;
• there are few other columns like foo ;
• tens of other columns like bar (with hard-coded data) on each view.

Answer 1

1. You aren't "Creating a column" like what you think in a view. A view doesn't hold any data, it's just a definition of a way you "View" the data in the underlying table that the view references.
2. You are suggesting that you ALTER the underlying table to match your VIEW B, but if you do that then the results that come in View A will be changed.
3. You are already combining your views in your UNION. Instead of referencing the views by name, you can reference them directly by the SQL you used to define the view. It's all the same.

So, in one query:

Create VIEW roipoussiere.view_c AS
SELECT
  concat_ws('view_a_', x, '_', y) AS foo,
  'Hello' AS bar,
  x,
  y
FROM roipoussiere.my_table
UNION ALL
SELECT
  concat_ws('view_b_', x, '_', y) AS foo,
  'Hello' AS bar,
  x,
  y
FROM roipoussiere.my_table;

Any time you find yourself creating views on top of views (on top of views on top of views) ask yourself if you really need those underlying views on their own. Will you ever execute ViewA by itself, or is it just there to make View C easier to write? If you don't need it, then don't make it, just do the SELECT in a subquery in the final view.

To get out of writing 'Hello' as bar and your other constant-type fields over and over again each of your SELECT statements that are union'd together, you can use a CTE (Common Table Expression) to define it once in your 1 view and use it over and over again.

CREATE VIEW roipoussiere.view_c as
WITH myCTE AS
(
    SELECT

      'Hello' AS bar,
      'Goodbye' as f1,
      'Another constant' as f2
      x,
      y
    FROM roipoussiere.my_table
)
SELECT
  concat_ws('view_a_', x, '_', y) AS foo,
  bar,
  f1,
  f2,
  x,
  y
FROM myCTE
UNION ALL
SELECT
  concat_ws('view_b_', x, '_', y) AS foo,
  bar,
  f1,
  f2,
  x,
  y
FROM myCTE