Views that inherit a table and syntax in PostgreSQL

Question

I am getting a syntax error but don't understand what is wrong.

I am using a reference here.

This is my sql-snippet:

CREATE OR REPLACE VIEW stipa_test.stipa_view
AS 
  (
    SELECT *
    FROM ...
    WHERE ...
  )
INHERITS (stipa_test.stipa_type_proj);

I hope you get the idea, everything but inheritance works. So how could it look like? both the subquery view and the type_proj table

Answer 1

Views cannot inherit.

Only tables can.

Aside: the query for CREATE VIEW does not need parentheses. And your whole SELECT is needlessly convoluted and buggy. Untangled and without the invalid INHERITS clause:

CREATE OR REPLACE VIEW stipa_test.stipa_view AS 
SELECT aar.gid AS aar_gid, aar.geom AS aar_geom 
     , aar_korr.gid AS aar_korr_gid, aar_korr.geom AS aar_korr_geom 
FROM   stipa_test.stipa_type_aar  aar
JOIN   stipa_test.stipa_type_aar_korr aar_korr
                   ON ST_Within(aar.geom, ST_Buffer(aar_korr.geom, 3))
-- and 'aar_korr.dato' > 'dato.aar'  -- nonsense, always FALSE

Answer 2

You have misunderstanding about the term inheritance as it applies in SQL.

You have a table structure with column

CREATE TABLE foo ( a int, b int, c int );

If you want a view that inherits from that table, you need to select from it explicitly. "Inherits" means something very specific in SQL parlance and it's not applicable to views. "Inheritance" in OOP refers to a method of eliminating redundancy by using data from a parent as a template. In SQL, "inheritance" is an implementation detail that should typically be avoided -- though sometimes useful in things like sharding.

So for instance, you can add a column d to table foo in a view by appending it on the query.

CREATE VIEW bar
AS
  SELECT foo.*, 'myval' AS d
  FROM foo;

You can read more about SQL-Inheritance in my answer ot Using table inheritance instead of mapping tables.