Index: doc/src/sgml/ref/create_table.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/create_table.sgml,v
retrieving revision 1.84
diff -c -r1.84 create_table.sgml
*** doc/src/sgml/ref/create_table.sgml 2 Aug 2004 04:25:31 -0000 1.84
--- doc/src/sgml/ref/create_table.sgml 26 Sep 2004 22:09:28 -0000
***************
*** 66,77 ****
If a schema name is given (for example, 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.
--- 66,77 ----
If a schema name is given (for example, 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.
Index: doc/src/sgml/ref/create_view.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/create_view.sgml,v
retrieving revision 1.28
diff -c -r1.28 create_view.sgml
*** doc/src/sgml/ref/create_view.sgml 29 Nov 2003 19:51:38 -0000 1.28
--- doc/src/sgml/ref/create_view.sgml 26 Sep 2004 22:09:28 -0000
***************
*** 20,26 ****
! CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query
--- 20,26 ----
! CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ] AS query
***************
*** 43,52 ****
If a schema name is given (for example, 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.
--- 43,54 ----
If a schema name is given (for example, 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.
***************
*** 55,60 ****
--- 57,84 ----
+ TEMPORARY or TEMP
+
+
+ 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.
+
+
+
+ If any of the base tables referenced by the view are temporary,
+ the view is created as a temporary view (whether
+ TEMPORARY is specified or not).
+
+
+
+
+
name
***************
*** 102,108 ****
! Use the DROP VIEW statement to drop views.
--- 126,133 ----
! Use the
! statement to drop views.
***************
*** 153,159 ****
CREATE VIEW name [ ( column [, ...] ) ]
AS query
! [ WITH [ CASCADE | LOCAL ] CHECK OPTION ]
--- 178,184 ----
CREATE VIEW name [ ( column [, ...] ) ]
AS query
! [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
***************
*** 184,195 ****
! CASCADE
Check for integrity on this view and on any dependent
! view. CASCADE is assumed if neither
! CASCADE nor LOCAL is specified.
--- 209,220 ----
! CASCADED
Check for integrity on this view and on any dependent
! view. CASCADED is assumed if neither
! CASCADED nor LOCAL is specified.
Index: src/backend/commands/view.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/commands/view.c,v
retrieving revision 1.85
diff -c -r1.85 view.c
*** src/backend/commands/view.c 29 Aug 2004 05:06:41 -0000 1.85
--- src/backend/commands/view.c 26 Sep 2004 22:09:28 -0000
***************
*** 21,26 ****
--- 21,28 ----
#include "commands/view.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
+ #include "nodes/pg_list.h"
+ #include "optimizer/clauses.h"
#include "parser/parse_relation.h"
#include "rewrite/rewriteDefine.h"
#include "rewrite/rewriteManip.h"
***************
*** 31,36 ****
--- 33,85 ----
static void checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc);
+ 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);
+ }
+
+ /*--------------------------------------------------------------
+ * isViewOnTempTable
+ *
+ * Returns true iff any of the relations underlying this view are
+ * temporary tables.
+ *---------------------------------------------------------------------
+ */
+ static bool
+ isViewOnTempTable(Query *viewParse)
+ {
+ return tempViewWalker((Node *) viewParse, NULL);
+ }
/*---------------------------------------------------------------------
* 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 explicitely ask for a temporary view, check
+ * whether we need one implicitely.
+ */
+ if (!view->istemp)
+ {
+ view->istemp = isViewOnTempTable(viewParse);
+ if ( view->istemp )
+ ereport(NOTICE,
+ (errmsg("view \"%s\" will be created in a temporary schema",
+ 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: /usr/local/cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.475
diff -c -r2.475 gram.y
*** src/backend/parser/gram.y 29 Aug 2004 04:12:35 -0000 2.475
--- src/backend/parser/gram.y 26 Sep 2004 22:09:28 -0000
***************
*** 4087,4108 ****
/*****************************************************************************
*
* QUERY:
! * create view '('target-list ')' AS
*
*****************************************************************************/
! 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;
}
;
-
/*****************************************************************************
*
--- 4087,4119 ----
/*****************************************************************************
*
* QUERY:
! * CREATE [ OR REPLACE ] [ TEMP ] VIEW '('target-list ')' AS
*
*****************************************************************************/
! 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;
}
;
/*****************************************************************************
*
Index: src/include/commands/view.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/include/commands/view.h,v
retrieving revision 1.20
diff -c -r1.20 view.h
*** src/include/commands/view.h 29 Aug 2004 04:13:06 -0000 1.20
--- src/include/commands/view.h 26 Sep 2004 22:09:28 -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: /usr/local/cvsroot/pgsql-server/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 26 Sep 2004 22:54:59 -0000
***************
*** 60,62 ****
--- 60,323 ----
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 created in a temporary schema
+ -- 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 created in a temporary schema
+ 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 created in a temporary schema
+ -- 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 created in a temporary schema
+ -- 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 created in a temporary schema
+ 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 created in a temporary schema
+ CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table);
+ NOTICE: view "v8_temp" will be created in a temporary schema
+ CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table);
+ NOTICE: view "v9_temp" will be created in a temporary schema
+ -- 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 created in a temporary schema
+ CREATE VIEW v11_temp AS SELECT t1.id, t2.a FROM base_table t1, v10_temp t2;
+ NOTICE: view "v11_temp" will be created in a temporary schema
+ CREATE VIEW v12_temp AS SELECT true FROM v11_temp;
+ NOTICE: view "v12_temp" will be created in a temporary schema
+ -- 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 created in a temporary schema
+ SELECT relname FROM pg_class
+ WHERE relname LIKE 'v_'
+ AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test');
+ 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%');
+ relname
+ ----------
+ v1_temp
+ v2_temp
+ v4_temp
+ v5_temp
+ v6_temp
+ v7_temp
+ v8_temp
+ v9_temp
+ v10_temp
+ v11_temp
+ v12_temp
+ v13_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 created in a temporary schema
+ 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 created in a temporary schema
+ CREATE VIEW nontemp3 AS SELECT * FROM t1 NATURAL INNER JOIN t2;
+ CREATE VIEW temporal3 AS SELECT * FROM t1 NATURAL INNER JOIN tt;
+ NOTICE: view "temporal3" will be created in a temporary schema
+ CREATE VIEW nontemp4 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2;
+ CREATE VIEW temporal4 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2;
+ NOTICE: view "temporal4" will be created in a temporary schema
+ CREATE VIEW nontemp5 AS SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num2;
+ CREATE VIEW temporal5 AS SELECT * FROM t1 RIGHT JOIN tt ON t1.num = tt.num2;
+ NOTICE: view "temporal5" will be created in a temporary schema
+ CREATE VIEW nontemp6 AS SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num2;
+ CREATE VIEW temporal6 AS SELECT * FROM t1 FULL JOIN tt ON t1.num = tt.num2;
+ NOTICE: view "temporal6" will be created in a temporary schema
+ CREATE VIEW nontemp7 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2 AND t2.value = 'xxx';
+ CREATE VIEW temporal7 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2 AND tt.value = 'xxx';
+ NOTICE: view "temporal7" will be created in a temporary schema
+ SELECT relname FROM pg_class
+ WHERE relname LIKE = 'nontemp%'
+ AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'testviewschm2');
+ ERROR: syntax error at or near "=" at character 53
+ LINE 2: WHERE relname LIKE = 'nontemp%'
+ ^
+ SELECT relname FROM pg_class
+ WHERE relname LIKE 'temporal%'
+ AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%');
+ relname
+ -----------
+ temporal1
+ temporal2
+ temporal3
+ temporal4
+ temporal5
+ temporal6
+ temporal7
+ (7 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 created in a temporary schema
+ 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 nontemp7
+ NOTICE: drop cascades to rule _RETURN on view nontemp7
+ NOTICE: drop cascades to view nontemp6
+ NOTICE: drop cascades to rule _RETURN on view nontemp6
+ NOTICE: drop cascades to view nontemp5
+ NOTICE: drop cascades to rule _RETURN on view nontemp5
+ 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 temporal7
+ NOTICE: drop cascades to view temporal7
+ NOTICE: drop cascades to rule _RETURN on view temporal6
+ NOTICE: drop cascades to view temporal6
+ NOTICE: drop cascades to rule _RETURN on view temporal5
+ NOTICE: drop cascades to view temporal5
+ 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: /usr/local/cvsroot/pgsql-server/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 26 Sep 2004 22:51:09 -0000
***************
*** 63,65 ****
--- 63,195 ----
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');
+ SELECT relname FROM pg_class
+ WHERE relname LIKE 'v%'
+ AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%');
+
+ 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 NATURAL INNER JOIN t2;
+ CREATE VIEW temporal3 AS SELECT * FROM t1 NATURAL INNER JOIN tt;
+ CREATE VIEW nontemp4 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2;
+ CREATE VIEW temporal4 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2;
+ CREATE VIEW nontemp5 AS SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num2;
+ CREATE VIEW temporal5 AS SELECT * FROM t1 RIGHT JOIN tt ON t1.num = tt.num2;
+ CREATE VIEW nontemp6 AS SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num2;
+ CREATE VIEW temporal6 AS SELECT * FROM t1 FULL JOIN tt ON t1.num = tt.num2;
+ CREATE VIEW nontemp7 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2 AND t2.value = 'xxx';
+ CREATE VIEW temporal7 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');
+ SELECT relname FROM pg_class
+ WHERE relname LIKE 'temporal%'
+ AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%');
+
+ 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;