On Tue, 2005-02-01 at 01:28 -0500, Tom Lane wrote:
> The references to "CASCADED" (sic) in the patch are surely bogus.

Per SQL:2003 section 11.22, it is spelt "CASCADED". I'm not sure there's
a whole lot of value in documenting syntax we don't support, but if
we're going to do it we may as well get it right.

> "tempViewWalker" in view.c is bereft of either comments or a usefully
> descriptive name.  Yeah, you find out what it is supposed to do when you
> reach the routine below, but the whole thing is poorly presented.  Waste
> a static declaration forward reference so you can put the documented
> routine first, and rename the walker to something based on the calling
> routine's name.

I've added the forward declaration, but I couldn't think of a better
name for the walker routine. isViewOnTempTableWalker() seemed too
clumsy; any suggestions?

> Does the gram.y change really require breaking out OR REPLACE as a
> separate production, and if so why?  That strikes me as something
> that should not be necessary ... if it is then there is some deeper
> problem to investigate.

Without a separate production, you get 8 shift/reduce conflicts because
both opt_or_replace and OptTemp can be reduced on the empty string. The
easiest workaround I can see is the one implemented in the patch, but I
won't claim to be a bison expert. Suggestions for improvement are
welcome.

> The regression tests seem a tad excessive --- I'll grant this is a
> matter of taste, but if every tiny little feature we have were tested
> like that, the regression tests would take days to run.

I've removed a few tests that didn't seem helpful. As for the tests
taking "days to run", that would be fine with me -- if and when the
tests actually _do_ take a long time to run, we can put more effort into
defining a subset of them that can be run more quickly. In the mean
time, though, I think we have far too few tests, not too many.

> [ If I were applying this I'd just editorialize on these things without
> comment, but if you want to do it then you get to do the cleanup... ]

Thanks for your comments. A revised version of the patch is attached.

-Neil

Index: doc/src/sgml/ref/create_table.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql/doc/src/sgml/ref/create_table.sgml,v
retrieving revision 1.91
diff -c -r1.91 create_table.sgml
*** doc/src/sgml/ref/create_table.sgml	22 Jan 2005 23:22:17 -0000	1.91
--- doc/src/sgml/ref/create_table.sgml	1 Feb 2005 23:32:45 -0000
***************
*** 66,77 ****
  
    <para>
     If a schema name is given (for example, <literal>CREATE TABLE
!    myschema.mytable ...</>) then the table is created in the
!    specified schema.  Otherwise it is created in the current schema.
!    Temporary tables exist in a special schema, so a schema name may not be
!    given when creating a temporary table.
!    The table name must be distinct from the name of any other table,
!    sequence, index, or view in the same schema.
    </para>
  
    <para>
--- 66,77 ----
  
    <para>
     If a schema name is given (for example, <literal>CREATE TABLE
!    myschema.mytable ...</>) then the table is created in the specified
!    schema.  Otherwise it is created in the current schema.  Temporary
!    tables exist in a special schema, so a schema name may not be given
!    when creating a temporary table.  The name of the table must be
!    distinct from the name of any other table, sequence, index, or view
!    in the same schema.
    </para>
  
    <para>
Index: doc/src/sgml/ref/create_view.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql/doc/src/sgml/ref/create_view.sgml,v
retrieving revision 1.29
diff -c -r1.29 create_view.sgml
*** doc/src/sgml/ref/create_view.sgml	4 Jan 2005 00:39:53 -0000	1.29
--- doc/src/sgml/ref/create_view.sgml	1 Feb 2005 23:32:45 -0000
***************
*** 20,26 ****
  
   <refsynopsisdiv>
  <synopsis>
! CREATE [ OR REPLACE ] VIEW <replaceable class="PARAMETER">name</replaceable> [ ( <replaceable
  class="PARAMETER">column_name</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">query</replaceable>
  </synopsis>
   </refsynopsisdiv>
--- 20,26 ----
  
   <refsynopsisdiv>
  <synopsis>
! CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW <replaceable class="PARAMETER">name</replaceable> [ ( <replaceable
  class="PARAMETER">column_name</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">query</replaceable>
  </synopsis>
   </refsynopsisdiv>
***************
*** 43,52 ****
  
    <para>
     If a schema name is given (for example, <literal>CREATE VIEW
!    myschema.myview ...</>) then the view is created in the
!    specified schema.  Otherwise it is created in the current schema.
!    The view name must be distinct from the name of any other view, table,
!    sequence, or index in the same schema.
    </para>
   </refsect1>
  
--- 43,54 ----
  
    <para>
     If a schema name is given (for example, <literal>CREATE VIEW
!    myschema.myview ...</>) then the view is created in the specified
!    schema.  Otherwise it is created in the current schema.  Temporary
!    views exist in a special schema, so a schema name may not be given
!    when creating a temporary view. The name of the view must be
!    distinct from the name of any other view, table, sequence, or index
!    in the same schema.
    </para>
   </refsect1>
  
***************
*** 55,60 ****
--- 57,84 ----
  
    <variablelist>
     <varlistentry>
+     <term><literal>TEMPORARY</> or <literal>TEMP</></term>
+     <listitem>
+      <para>
+       If specified, the view is created as a temporary view.
+       Temporary views are automatically dropped at the end of the
+       current session. Temporary views are automatically placed in the
+       current backend's local temporary schema, so it is illegal to
+       specify a schema-qualified name for a temporary view. Existing
+       permanent relations with the same name are not visible to the
+       current session while the temporary view exists, unless they are
+       referenced with schema-qualified names.
+      </para>
+ 
+      <para>
+       If any of the base tables referenced by the view are temporary,
+       the view is created as a temporary view (whether
+       <literal>TEMPORARY</literal> is specified or not).
+      </para>
+     </listitem>
+    </varlistentry>
+    
+    <varlistentry>
      <term><replaceable class="parameter">name</replaceable></term>
      <listitem>
       <para>
***************
*** 102,108 ****
     </para>
  
     <para>
!     Use the <command>DROP VIEW</command> statement to drop views.
     </para>
  
     <para>
--- 126,133 ----
     </para>
  
     <para>
!     Use the <xref linkend="sql-dropview" endterm="sql-dropview-title">
!     statement to drop views.
     </para>
  
     <para>
***************
*** 153,159 ****
  <synopsis>
  CREATE VIEW <replaceable class="parameter">name</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ]
      AS query
!     [ WITH [ CASCADE | LOCAL ] CHECK OPTION ]
  </synopsis>
    </para>
  
--- 178,184 ----
  <synopsis>
  CREATE VIEW <replaceable class="parameter">name</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ]
      AS query
!     [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
  </synopsis>
    </para>
  
***************
*** 184,195 ****
       </varlistentry>
  
       <varlistentry>
!       <term><literal>CASCADE</literal></term>
        <listitem>
         <para>
          Check for integrity on this view and on any dependent
!         view. <literal>CASCADE</> is assumed if neither
!         <literal>CASCADE</> nor <literal>LOCAL</> is specified.
         </para>
        </listitem>
       </varlistentry>
--- 209,220 ----
       </varlistentry>
  
       <varlistentry>
!       <term><literal>CASCADED</literal></term>
        <listitem>
         <para>
          Check for integrity on this view and on any dependent
!         view. <literal>CASCADED</> is assumed if neither
!         <literal>CASCADED</> nor <literal>LOCAL</> is specified.
         </para>
        </listitem>
       </varlistentry>
Index: src/backend/commands/view.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/commands/view.c,v
retrieving revision 1.86
diff -c -r1.86 view.c
*** src/backend/commands/view.c	31 Dec 2004 21:59:42 -0000	1.86
--- src/backend/commands/view.c	2 Feb 2005 01:11:17 -0000
***************
*** 21,26 ****
--- 21,27 ----
  #include "commands/view.h"
  #include "miscadmin.h"
  #include "nodes/makefuncs.h"
+ #include "optimizer/clauses.h"
  #include "parser/parse_relation.h"
  #include "rewrite/rewriteDefine.h"
  #include "rewrite/rewriteManip.h"
***************
*** 30,36 ****
--- 31,85 ----
  
  
  static void checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc);
+ static bool tempViewWalker(Node *node, void *context);
  
+ /*---------------------------------------------------------------------
+  * isViewOnTempTable
+  *
+  * Returns true iff any of the relations underlying this view are
+  * temporary tables.
+  *---------------------------------------------------------------------
+  */
+ static bool
+ isViewOnTempTable(Query *viewParse)
+ {
+ 	return tempViewWalker((Node *) viewParse, NULL);
+ }
+ 
+ static bool
+ tempViewWalker(Node *node, void *context)
+ {
+ 	if (node == NULL)
+ 		return false;
+ 
+ 	if (IsA(node, Query))
+ 	{
+ 		Query		*query = (Query *) node;
+ 		ListCell	*rtable;
+ 
+ 		foreach (rtable, query->rtable)
+ 		{
+ 			RangeTblEntry *rte = lfirst(rtable);
+ 			if (rte->rtekind == RTE_RELATION)
+ 			{
+ 				Relation rel = heap_open(rte->relid, AccessShareLock);
+ 				bool istemp = rel->rd_istemp;
+ 				heap_close(rel, AccessShareLock);
+ 				if (istemp)
+ 					return true;
+ 			}
+ 		}
+ 
+ 		return query_tree_walker(query,
+ 								 tempViewWalker,
+ 								 context,
+ 								 QTW_IGNORE_JOINALIASES);
+ 	}
+ 
+ 	return expression_tree_walker(node,
+ 								  tempViewWalker,
+ 								  context);
+ }
  
  /*---------------------------------------------------------------------
   * DefineVirtualRelation
***************
*** 118,123 ****
--- 167,179 ----
  						   RelationGetRelationName(rel));
  
  		/*
+ 		 * Due to the namespace visibility rules for temporary
+ 		 * objects, we should only end up replacing a temporary view
+ 		 * with another temporary view, and vice versa.
+ 		 */
+ 		Assert(relation->istemp == rel->rd_istemp);
+ 
+ 		/*
  		 * Create a tuple descriptor to compare against the existing view,
  		 * and verify it matches.
  		 */
***************
*** 326,342 ****
   *-------------------------------------------------------------------
   */
  void
! DefineView(const RangeVar *view, Query *viewParse, bool replace)
  {
  	Oid			viewOid;
  
  	/*
  	 * Create the view relation
  	 *
  	 * NOTE: if it already exists and replace is false, the xact will be
  	 * aborted.
  	 */
- 
  	viewOid = DefineVirtualRelation(view, viewParse->targetList, replace);
  
  	/*
--- 382,410 ----
   *-------------------------------------------------------------------
   */
  void
! DefineView(RangeVar *view, Query *viewParse, bool replace)
  {
  	Oid			viewOid;
  
  	/*
+ 	 * If the user didn't explicitly ask for a temporary view, check
+ 	 * whether we need one implicitly.
+ 	 */
+ 	if (!view->istemp)
+ 	{
+ 		view->istemp = isViewOnTempTable(viewParse);
+ 		if (view->istemp)
+ 			ereport(NOTICE,
+ 					(errmsg("view \"%s\" will be a temporary view",
+ 							view->relname)));
+ 	}
+ 		
+ 	/*
  	 * Create the view relation
  	 *
  	 * NOTE: if it already exists and replace is false, the xact will be
  	 * aborted.
  	 */
  	viewOid = DefineVirtualRelation(view, viewParse->targetList, replace);
  
  	/*
Index: src/backend/parser/gram.y
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.482
diff -c -r2.482 gram.y
*** src/backend/parser/gram.y	27 Jan 2005 03:17:59 -0000	2.482
--- src/backend/parser/gram.y	2 Feb 2005 01:06:55 -0000
***************
*** 4075,4098 ****
  
  /*****************************************************************************
   *
!  *		QUERY:
!  *				create view <viewname> '('target-list ')' AS <query>
   *
   *****************************************************************************/
  
! ViewStmt:	CREATE opt_or_replace VIEW qualified_name opt_column_list
  				AS SelectStmt
  				{
  					ViewStmt *n = makeNode(ViewStmt);
! 					n->replace = $2;
  					n->view = $4;
  					n->aliases = $5;
  					n->query = (Query *) $7;
! 					$$ = (Node *)n;
  				}
  		;
  
- 
  /*****************************************************************************
   *
   *		QUERY:
--- 4075,4109 ----
  
  /*****************************************************************************
   *
!  *	QUERY:
!  *		CREATE [ OR REPLACE ] [ TEMP ] VIEW <viewname> '('target-list ')' AS <query>
   *
   *****************************************************************************/
  
! ViewStmt: CREATE OR REPLACE OptTemp VIEW qualified_name opt_column_list
  				AS SelectStmt
  				{
  					ViewStmt *n = makeNode(ViewStmt);
! 					n->replace = true;
! 					n->view = $6;
! 					n->view->istemp = $4;
! 					n->aliases = $7;
! 					n->query = (Query *) $9;
! 					$$ = (Node *) n;
! 				}
! 		| CREATE OptTemp VIEW qualified_name opt_column_list
! 				AS SelectStmt
! 				{
! 					ViewStmt *n = makeNode(ViewStmt);
! 					n->replace = false;
  					n->view = $4;
+ 					n->view->istemp = $2;
  					n->aliases = $5;
  					n->query = (Query *) $7;
! 					$$ = (Node *) n;
  				}
  		;
  
  /*****************************************************************************
   *
   *		QUERY:
Index: src/include/commands/view.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/commands/view.h,v
retrieving revision 1.21
diff -c -r1.21 view.h
*** src/include/commands/view.h	31 Dec 2004 22:03:28 -0000	1.21
--- src/include/commands/view.h	1 Feb 2005 23:32:45 -0000
***************
*** 16,22 ****
  
  #include "nodes/parsenodes.h"
  
! extern void DefineView(const RangeVar *view, Query *view_parse, bool replace);
  extern void RemoveView(const RangeVar *view, DropBehavior behavior);
  
  #endif   /* VIEW_H */
--- 16,22 ----
  
  #include "nodes/parsenodes.h"
  
! extern void DefineView(RangeVar *view, Query *view_parse, bool replace);
  extern void RemoveView(const RangeVar *view, DropBehavior behavior);
  
  #endif   /* VIEW_H */
Index: src/test/regress/expected/create_view.out
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/expected/create_view.out,v
retrieving revision 1.8
diff -c -r1.8 create_view.out
*** src/test/regress/expected/create_view.out	21 Nov 2003 22:32:49 -0000	1.8
--- src/test/regress/expected/create_view.out	2 Feb 2005 01:00:36 -0000
***************
*** 60,62 ****
--- 60,308 ----
  ERROR:  cannot change data type of view column "b"
  DROP VIEW viewtest;
  DROP TABLE viewtest_tbl;
+ -- tests for temporary views
+ CREATE SCHEMA temp_view_test
+     CREATE TABLE base_table (a int, id int)
+     CREATE TABLE base_table2 (a int, id int);
+ SET search_path TO temp_view_test, public;
+ CREATE TEMPORARY TABLE temp_table (a int, id int);
+ -- should be created in temp_view_test schema
+ CREATE VIEW v1 AS SELECT * FROM base_table;
+ -- should be created in temp object schema
+ CREATE VIEW v1_temp AS SELECT * FROM temp_table;
+ NOTICE:  view "v1_temp" will be a temporary view
+ -- should be created in temp object schema
+ CREATE TEMP VIEW v2_temp AS SELECT * FROM base_table;
+ -- should be created in temp_views schema
+ CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table;
+ -- should fail
+ CREATE VIEW temp_view_test.v3_temp AS SELECT * FROM temp_table;
+ NOTICE:  view "v3_temp" will be a temporary view
+ ERROR:  temporary tables may not specify a schema name
+ -- should fail
+ CREATE SCHEMA test_schema
+     CREATE TEMP VIEW testview AS SELECT 1;
+ ERROR:  temporary tables may not specify a schema name
+ -- joins: if any of the join relations are temporary, the view
+ -- should also be temporary
+ -- should be non-temp
+ CREATE VIEW v3 AS
+     SELECT t1.a AS t1_a, t2.a AS t2_a
+     FROM base_table t1, base_table2 t2
+     WHERE t1.id = t2.id;
+ -- should be temp (one join rel is temp)
+ CREATE VIEW v4_temp AS
+     SELECT t1.a AS t1_a, t2.a AS t2_a
+     FROM base_table t1, temp_table t2
+     WHERE t1.id = t2.id;
+ NOTICE:  view "v4_temp" will be a temporary view
+ -- should be temp
+ CREATE VIEW v5_temp AS
+     SELECT t1.a AS t1_a, t2.a AS t2_a, t3.a AS t3_a
+     FROM base_table t1, base_table2 t2, temp_table t3
+     WHERE t1.id = t2.id and t2.id = t3.id;
+ NOTICE:  view "v5_temp" will be a temporary view
+ -- subqueries
+ CREATE VIEW v4 AS SELECT * FROM base_table WHERE id IN (SELECT id FROM base_table2);
+ CREATE VIEW v5 AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM base_table2) t2;
+ CREATE VIEW v6 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM base_table2);
+ CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_table2);
+ CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1);
+ CREATE VIEW v6_temp AS SELECT * FROM base_table WHERE id IN (SELECT id FROM temp_table);
+ NOTICE:  view "v6_temp" will be a temporary view
+ CREATE VIEW v7_temp AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM temp_table) t2;
+ NOTICE:  view "v7_temp" will be a temporary view
+ CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table);
+ NOTICE:  view "v8_temp" will be a temporary view
+ CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table);
+ NOTICE:  view "v9_temp" will be a temporary view
+ -- a view should also be temporary if it references a temporary view
+ CREATE VIEW v10_temp AS SELECT * FROM v7_temp;
+ NOTICE:  view "v10_temp" will be a temporary view
+ CREATE VIEW v11_temp AS SELECT t1.id, t2.a FROM base_table t1, v10_temp t2;
+ NOTICE:  view "v11_temp" will be a temporary view
+ CREATE VIEW v12_temp AS SELECT true FROM v11_temp;
+ NOTICE:  view "v12_temp" will be a temporary view
+ -- a view should also be temporary if it references a temporary sequence
+ CREATE SEQUENCE seq1;
+ CREATE TEMPORARY SEQUENCE seq1_temp;
+ CREATE VIEW v9 AS SELECT seq1.is_called;
+ CREATE VIEW v13_temp AS SELECT seq1_temp.is_called;
+ NOTICE:  view "v13_temp" will be a temporary view
+ SELECT relname FROM pg_class
+     WHERE relname LIKE 'v_'  
+     AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test')
+     ORDER BY relname;
+  relname 
+ ---------
+  v1
+  v2
+  v3
+  v4
+  v5
+  v6
+  v7
+  v8
+  v9
+ (9 rows)
+ 
+ SELECT relname FROM pg_class
+     WHERE relname LIKE 'v%' 
+     AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
+     ORDER BY relname;
+  relname  
+ ----------
+  v10_temp
+  v11_temp
+  v12_temp
+  v13_temp
+  v1_temp
+  v2_temp
+  v4_temp
+  v5_temp
+  v6_temp
+  v7_temp
+  v8_temp
+  v9_temp
+ (12 rows)
+ 
+ CREATE SCHEMA testviewschm2;
+ SET search_path TO testviewschm2, public;
+ CREATE TABLE t1 (num int, name text);
+ CREATE TABLE t2 (num2 int, value text);
+ CREATE TEMP TABLE tt (num2 int, value text);
+ CREATE VIEW nontemp1 AS SELECT * FROM t1 CROSS JOIN t2;
+ CREATE VIEW temporal1 AS SELECT * FROM t1 CROSS JOIN tt;
+ NOTICE:  view "temporal1" will be a temporary view
+ CREATE VIEW nontemp2 AS SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num2;
+ CREATE VIEW temporal2 AS SELECT * FROM t1 INNER JOIN tt ON t1.num = tt.num2;
+ NOTICE:  view "temporal2" will be a temporary view
+ CREATE VIEW nontemp3 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2;
+ CREATE VIEW temporal3 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2;
+ NOTICE:  view "temporal3" will be a temporary view
+ CREATE VIEW nontemp4 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2 AND t2.value = 'xxx';
+ CREATE VIEW temporal4 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2 AND tt.value = 'xxx';
+ NOTICE:  view "temporal4" will be a temporary view
+ SELECT relname FROM pg_class
+     WHERE relname LIKE 'nontemp%'
+     AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'testviewschm2')
+     ORDER BY relname;
+  relname  
+ ----------
+  nontemp1
+  nontemp2
+  nontemp3
+  nontemp4
+ (4 rows)
+ 
+ SELECT relname FROM pg_class
+     WHERE relname LIKE 'temporal%' 
+     AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
+     ORDER BY relname;
+   relname  
+ -----------
+  temporal1
+  temporal2
+  temporal3
+  temporal4
+ (4 rows)
+ 
+ CREATE TABLE tbl1 ( a int, b int);
+ CREATE TABLE tbl2 (c int, d int);
+ CREATE TABLE tbl3 (e int, f int);
+ CREATE TABLE tbl4 (g int, h int);
+ CREATE TEMP TABLE tmptbl (i int, j int);
+ --Should be in testviewschm2
+ CREATE   VIEW  pubview AS SELECT * FROM tbl1 WHERE tbl1.a
+ BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
+ AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f);
+ SELECT count(*) FROM pg_class where relname = 'pubview'
+ AND relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname = 'testviewschm2');
+  count 
+ -------
+      1
+ (1 row)
+ 
+ --Should be in temp object schema
+ CREATE   VIEW  mytempview AS SELECT * FROM tbl1 WHERE tbl1.a
+ BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
+ AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f)
+ AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j);
+ NOTICE:  view "mytempview" will be a temporary view
+ SELECT count(*) FROM pg_class where relname LIKE 'mytempview'
+ And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%');
+  count 
+ -------
+      1
+ (1 row)
+ 
+ DROP SCHEMA temp_view_test CASCADE;
+ NOTICE:  drop cascades to view temp_view_test.v9
+ NOTICE:  drop cascades to rule _RETURN on view temp_view_test.v9
+ NOTICE:  drop cascades to sequence temp_view_test.seq1
+ NOTICE:  drop cascades to view temp_view_test.v8
+ NOTICE:  drop cascades to rule _RETURN on view temp_view_test.v8
+ NOTICE:  drop cascades to view temp_view_test.v7
+ NOTICE:  drop cascades to rule _RETURN on view temp_view_test.v7
+ NOTICE:  drop cascades to view temp_view_test.v6
+ NOTICE:  drop cascades to rule _RETURN on view temp_view_test.v6
+ NOTICE:  drop cascades to view temp_view_test.v5
+ NOTICE:  drop cascades to rule _RETURN on view temp_view_test.v5
+ NOTICE:  drop cascades to view temp_view_test.v4
+ NOTICE:  drop cascades to rule _RETURN on view temp_view_test.v4
+ NOTICE:  drop cascades to view temp_view_test.v3
+ NOTICE:  drop cascades to rule _RETURN on view temp_view_test.v3
+ NOTICE:  drop cascades to view temp_view_test.v2
+ NOTICE:  drop cascades to rule _RETURN on view temp_view_test.v2
+ NOTICE:  drop cascades to view temp_view_test.v1
+ NOTICE:  drop cascades to rule _RETURN on view temp_view_test.v1
+ NOTICE:  drop cascades to table temp_view_test.base_table2
+ NOTICE:  drop cascades to rule _RETURN on view v5_temp
+ NOTICE:  drop cascades to view v5_temp
+ NOTICE:  drop cascades to table temp_view_test.base_table
+ NOTICE:  drop cascades to rule _RETURN on view v9_temp
+ NOTICE:  drop cascades to view v9_temp
+ NOTICE:  drop cascades to rule _RETURN on view v8_temp
+ NOTICE:  drop cascades to view v8_temp
+ NOTICE:  drop cascades to rule _RETURN on view v6_temp
+ NOTICE:  drop cascades to view v6_temp
+ NOTICE:  drop cascades to rule _RETURN on view v4_temp
+ NOTICE:  drop cascades to view v4_temp
+ NOTICE:  drop cascades to rule _RETURN on view v2_temp
+ NOTICE:  drop cascades to view v2_temp
+ NOTICE:  drop cascades to rule _RETURN on view v11_temp
+ NOTICE:  drop cascades to view v11_temp
+ NOTICE:  drop cascades to rule _RETURN on view v12_temp
+ NOTICE:  drop cascades to view v12_temp
+ NOTICE:  drop cascades to rule _RETURN on view v7_temp
+ NOTICE:  drop cascades to view v7_temp
+ NOTICE:  drop cascades to rule _RETURN on view v10_temp
+ NOTICE:  drop cascades to view v10_temp
+ DROP SCHEMA testviewschm2 CASCADE;
+ NOTICE:  drop cascades to view pubview
+ NOTICE:  drop cascades to rule _RETURN on view pubview
+ NOTICE:  drop cascades to table tbl4
+ NOTICE:  drop cascades to rule _RETURN on view mytempview
+ NOTICE:  drop cascades to view mytempview
+ NOTICE:  drop cascades to table tbl3
+ NOTICE:  drop cascades to table tbl2
+ NOTICE:  drop cascades to table tbl1
+ NOTICE:  drop cascades to view nontemp4
+ NOTICE:  drop cascades to rule _RETURN on view nontemp4
+ NOTICE:  drop cascades to view nontemp3
+ NOTICE:  drop cascades to rule _RETURN on view nontemp3
+ NOTICE:  drop cascades to view nontemp2
+ NOTICE:  drop cascades to rule _RETURN on view nontemp2
+ NOTICE:  drop cascades to view nontemp1
+ NOTICE:  drop cascades to rule _RETURN on view nontemp1
+ NOTICE:  drop cascades to table t2
+ NOTICE:  drop cascades to table t1
+ NOTICE:  drop cascades to rule _RETURN on view temporal4
+ NOTICE:  drop cascades to view temporal4
+ NOTICE:  drop cascades to rule _RETURN on view temporal3
+ NOTICE:  drop cascades to view temporal3
+ NOTICE:  drop cascades to rule _RETURN on view temporal2
+ NOTICE:  drop cascades to view temporal2
+ NOTICE:  drop cascades to rule _RETURN on view temporal1
+ NOTICE:  drop cascades to view temporal1
+ SET search_path to public;
Index: src/test/regress/sql/create_view.sql
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/sql/create_view.sql,v
retrieving revision 1.5
diff -c -r1.5 create_view.sql
*** src/test/regress/sql/create_view.sql	21 Nov 2003 22:32:49 -0000	1.5
--- src/test/regress/sql/create_view.sql	2 Feb 2005 00:56:11 -0000
***************
*** 63,65 ****
--- 63,193 ----
  
  DROP VIEW viewtest;
  DROP TABLE viewtest_tbl;
+ 
+ -- tests for temporary views
+ 
+ CREATE SCHEMA temp_view_test
+     CREATE TABLE base_table (a int, id int)
+     CREATE TABLE base_table2 (a int, id int);
+ 
+ SET search_path TO temp_view_test, public;
+ 
+ CREATE TEMPORARY TABLE temp_table (a int, id int);
+ 
+ -- should be created in temp_view_test schema
+ CREATE VIEW v1 AS SELECT * FROM base_table;
+ -- should be created in temp object schema
+ CREATE VIEW v1_temp AS SELECT * FROM temp_table;
+ -- should be created in temp object schema
+ CREATE TEMP VIEW v2_temp AS SELECT * FROM base_table;
+ -- should be created in temp_views schema
+ CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table;
+ -- should fail
+ CREATE VIEW temp_view_test.v3_temp AS SELECT * FROM temp_table;
+ -- should fail
+ CREATE SCHEMA test_schema
+     CREATE TEMP VIEW testview AS SELECT 1;
+ 
+ -- joins: if any of the join relations are temporary, the view
+ -- should also be temporary
+ 
+ -- should be non-temp
+ CREATE VIEW v3 AS
+     SELECT t1.a AS t1_a, t2.a AS t2_a
+     FROM base_table t1, base_table2 t2
+     WHERE t1.id = t2.id;
+ -- should be temp (one join rel is temp)
+ CREATE VIEW v4_temp AS
+     SELECT t1.a AS t1_a, t2.a AS t2_a
+     FROM base_table t1, temp_table t2
+     WHERE t1.id = t2.id;
+ -- should be temp
+ CREATE VIEW v5_temp AS
+     SELECT t1.a AS t1_a, t2.a AS t2_a, t3.a AS t3_a
+     FROM base_table t1, base_table2 t2, temp_table t3
+     WHERE t1.id = t2.id and t2.id = t3.id;
+ 
+ -- subqueries
+ CREATE VIEW v4 AS SELECT * FROM base_table WHERE id IN (SELECT id FROM base_table2);
+ CREATE VIEW v5 AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM base_table2) t2;
+ CREATE VIEW v6 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM base_table2);
+ CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_table2);
+ CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1);
+ 
+ CREATE VIEW v6_temp AS SELECT * FROM base_table WHERE id IN (SELECT id FROM temp_table);
+ CREATE VIEW v7_temp AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM temp_table) t2;
+ CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table);
+ CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table);
+ 
+ -- a view should also be temporary if it references a temporary view
+ CREATE VIEW v10_temp AS SELECT * FROM v7_temp;
+ CREATE VIEW v11_temp AS SELECT t1.id, t2.a FROM base_table t1, v10_temp t2;
+ CREATE VIEW v12_temp AS SELECT true FROM v11_temp;
+ 
+ -- a view should also be temporary if it references a temporary sequence
+ CREATE SEQUENCE seq1;
+ CREATE TEMPORARY SEQUENCE seq1_temp;
+ CREATE VIEW v9 AS SELECT seq1.is_called;
+ CREATE VIEW v13_temp AS SELECT seq1_temp.is_called;
+ 
+ SELECT relname FROM pg_class
+     WHERE relname LIKE 'v_'  
+     AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test')
+     ORDER BY relname;
+ SELECT relname FROM pg_class
+     WHERE relname LIKE 'v%' 
+     AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
+     ORDER BY relname;
+ 
+ CREATE SCHEMA testviewschm2;
+ SET search_path TO testviewschm2, public;
+ 
+ CREATE TABLE t1 (num int, name text);
+ CREATE TABLE t2 (num2 int, value text);
+ CREATE TEMP TABLE tt (num2 int, value text);
+ 
+ CREATE VIEW nontemp1 AS SELECT * FROM t1 CROSS JOIN t2;
+ CREATE VIEW temporal1 AS SELECT * FROM t1 CROSS JOIN tt;
+ CREATE VIEW nontemp2 AS SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num2;
+ CREATE VIEW temporal2 AS SELECT * FROM t1 INNER JOIN tt ON t1.num = tt.num2;
+ CREATE VIEW nontemp3 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2;
+ CREATE VIEW temporal3 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2;
+ CREATE VIEW nontemp4 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2 AND t2.value = 'xxx';
+ CREATE VIEW temporal4 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2 AND tt.value = 'xxx';
+ 
+ SELECT relname FROM pg_class
+     WHERE relname LIKE 'nontemp%'
+     AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'testviewschm2')
+     ORDER BY relname;
+ SELECT relname FROM pg_class
+     WHERE relname LIKE 'temporal%' 
+     AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
+     ORDER BY relname;
+ 
+ CREATE TABLE tbl1 ( a int, b int);
+ CREATE TABLE tbl2 (c int, d int);
+ CREATE TABLE tbl3 (e int, f int);
+ CREATE TABLE tbl4 (g int, h int);
+ CREATE TEMP TABLE tmptbl (i int, j int);
+ 
+ --Should be in testviewschm2
+ CREATE   VIEW  pubview AS SELECT * FROM tbl1 WHERE tbl1.a
+ BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
+ AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f);
+ 
+ SELECT count(*) FROM pg_class where relname = 'pubview'
+ AND relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname = 'testviewschm2');
+ 
+ --Should be in temp object schema
+ CREATE   VIEW  mytempview AS SELECT * FROM tbl1 WHERE tbl1.a
+ BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
+ AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f)
+ AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j);
+ 
+ SELECT count(*) FROM pg_class where relname LIKE 'mytempview'
+ And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%');
+ 
+ DROP SCHEMA temp_view_test CASCADE;
+ DROP SCHEMA testviewschm2 CASCADE;
+ 
+ SET search_path to public;
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to