Thread

1. Create recursive view schema.name

   Lele Gaifax <lele@metapensiero.it> — 2016-10-11T07:36:37Z

   Hi all,
   
   I'm using PG 9.6, learning the "recursive" queries.
   
   I have a working recursive-CTE query, and I tried wrapping it in a view:
   reading the documentation I found the "CREATE RECURSIVE VIEW" form, so I tried
   it.
   
   It works as far as I use a "simple" name for the view:
   
     CREATE OR REPLACE RECURSIVE VIEW procedure_sites (procedure_id, site_id) AS
       SELECT pr.id, pr.site_id
       FROM sop.procedures pr
         JOIN sop.phases ph on ph.procedure_id = pr.id
   
       UNION ALL
   
       SELECT s.procedure_id, ss.site_id
       FROM procedure_sites s
         JOIN risk.company_sites ss ON ss.id = s.site_id
       WHERE ss.site_id IS NOT NULL;
   
   but I get an error when I create it in a specific schema:
   
     CREATE OR REPLACE RECURSIVE VIEW sop.procedure_sites (procedure_id, site_id) AS
       SELECT pr.id, pr.site_id
       FROM sop.procedures pr
         JOIN sop.phases ph on ph.procedure_id = pr.id
   
       UNION ALL
   
       SELECT s.procedure_id, ss.site_id
       FROM sop.procedure_sites s
         JOIN risk.company_sites ss ON ss.id = s.site_id
       WHERE ss.site_id IS NOT NULL;
   
     ERROR:  relation "sop.procedure_sites" does not exist
     RIGA 8:     JOIN sop.procedure_sites s ON s.site_id = ss.id
                      ^
                      
   Am I missing something?
   
   Thanks in advance for any hint,
   ciao, lele.
   -- 
   nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
   real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
   lele@metapensiero.it  |                 -- Fortunato Depero, 1929.
   
   
   
   

2. Re: Create recursive view schema.name

   Tom Lane <tgl@sss.pgh.pa.us> — 2016-10-11T13:46:13Z

   Lele Gaifax <lele@metapensiero.it> writes:
   > I have a working recursive-CTE query, and I tried wrapping it in a view:
   > reading the documentation I found the "CREATE RECURSIVE VIEW" form, so I tried
   > it.
   
   > It works as far as I use a "simple" name for the view:
   > but I get an error when I create it in a specific schema:
   
   The manual says
   	CREATE RECURSIVE VIEW name (columns) AS SELECT ...;
   	is equivalent to
   	CREATE VIEW name AS WITH RECURSIVE name (columns) AS (SELECT ...) SELECT columns FROM name;
   
   I guess it could be more explicit about the fact that the implied CTE just
   has the base name of the view; but since CTE names can't be qualified,
   that's not that hard to guess.  Short answer is that you don't qualify the
   view's internal self-reference, even if you are using a schema name in the
   CREATE.
   
   			regards, tom lane
   
   
   

3. Re: Create recursive view schema.name

   Lele Gaifax <lele@metapensiero.it> — 2016-10-11T14:12:58Z

   Tom Lane <tgl@sss.pgh.pa.us> writes:
   
   > The manual says
   > 	CREATE RECURSIVE VIEW name (columns) AS SELECT ...;
   > 	is equivalent to
   > 	CREATE VIEW name AS WITH RECURSIVE name (columns) AS (SELECT ...) SELECT columns FROM name;
   >
   > I guess it could be more explicit about the fact that the implied CTE just
   > has the base name of the view; but since CTE names can't be qualified,
   > that's not that hard to guess.  Short answer is that you don't qualify the
   > view's internal self-reference, even if you are using a schema name in the
   > CREATE.
   
   Thank you Tom, it works.
   
   I agree with you that the doc could/should be fixed/enhanced, because the
   explanation of "name" is immediately following the snippet you cited, and it
   says "The name (optionally schema-qualified) of a view to be created": it
   would never occurred to me that I could use a not-qualified name within the
   view.
   
   bye, lele.
   -- 
   nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
   real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
   lele@metapensiero.it  |                 -- Fortunato Depero, 1929.