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
