Créer une vue à partir d'une autre

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.]

DROP VIEW IF EXISTS roipoussiere.view_a CASCADE;
CREATE VIEW roipoussiere.view_a AS SELECT
  '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
  'view_b_' || '_' || x || '_' || y AS foo,
  'Hello' AS bar,
  x,
  y
FROM roipoussiere.my_table;

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;

CREATE VIEW roipoussiere.my_view as
WITH cte AS
(
    SELECT
      'Hello' AS bar,
      x,
      y
    FROM roipoussiere.my_table
)
SELECT
  concat_ws('_', 'view_a', x, y) AS foo,
  bar, x, y
FROM cte
UNION ALL
SELECT
  concat_ws('_', 'view_b', x, y) AS foo,
  bar, x, y
FROM cte