roipoussiere roipoussiere - 1 month ago 7
SQL Question

How to create a view from an other one?

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