WITH Elements in CREATE VIEW
The WITH syntax of a CTE is not only applicable to queries but can also appear in contexts like CREATE TABLE AS SELECT or CREATE VIEW. For example:
CREATE VIEW issibling AS
WITH
ismother(name, child) AS (
SELECT parent,r.name
FROM person p JOIN relatives r ON p.name=r.parent AND gender='F'
),
isfather(name, child) AS (
SELECT parent,r.name
FROM person p JOIN relatives r ON p.name=r.parent AND gender='M'
),
isparent(name, child) AS (
SELECT f.name, f.child FROM isfather f
UNION ALL
SELECT m.name, m.child FROM ismother m
),
sharefather(name,sibling,father) AS (
SELECT p2.name,p3.name,a.name
FROM person p2,person p3,isfather a JOIN isfather b ON a.name=b.name
WHERE p2.name<>p3.name AND a.child=p2.name AND b.child=p3.name
),
sharemother(name,sibling,mother) AS (
SELECT p2.name,p3.name,a.name
FROM person p2, person p3,ismother a JOIN ismother b ON a.name=b.name
WHERE p2.name<>p3.name AND a.child=p2.name AND b.child=p3.name
),
issibling AS (
SELECT sf.name,sf.sibling,sf.father,sm.mother
FROM sharefather sf JOIN sharemother sm
ON sf.name=sm.name AND sf.sibling=sm.sibling
)
SELECT * FROM issibling;
Note: In this example the defined view name happens to be the same as one of the WITH elements in the query. There is no conflict because the WITH element names remain local to the query.