temporary view from TODO list
Started by Koju Iijimaover 21 years ago9 messages
Dear Hackers,
I would like to implement and contribute two TODO items:
"Have views on temporary tables exist in the temporary namespace" and
"Allow temporary views on non-temporary tables".
I would like to discuss several points with community.
*Synopsis
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW ..........
*Description
If [TEMP | TEMPORARY] is specified, the view is created in a temporary
namespace.
If any of the base tables referenced by the view are temporary, the view is
created in a temporary namespace, even if the [TEMP | TEMPORARY] is not
specified (in this case, I'm planning to give "NOTICE" to the client).
*test items for RT
I would like to know amount of test items and what the intention of those
items should be.
1. few items that should test only all the codes we modified or added, to
find any errors from those codes.
2. various items, possibly all the cases that CREATE VIEW operation
involves, to find any errors from temporary view related operations.
Look forward to your comments.
Thanks
koju
----------------------------------------------------------------------------
---
Koju Iijima
Software Engineer
Fujitsu Australia Software Technology
Address: 14 Rodborough Road, Frenchs Forest NSW 2086
Tel: +61 2 9452 9076
Fax: +61 2 9975 2899
Email: koju@fast.fujitsu.com.au
Web site: www.fastware.com
----------------------------------------------------------------------------
---
This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinary user of the email address to which it was addressed and may contain copyright and/or legally privileged information. No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive this email in error, please return to sender. Thank you.
If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please email unsubscribe@fast.fujitsu.com.au
patch for temporary view from TODO list
Dear community,
I have implemented temporary view related items as I proposed few days ago.
http://archives.postgresql.org/pgsql-hackers/2004-09/msg00682.php
About test queries for RT (create_view.sql), all queries I added were to
find any errors that may be caused by temporary view related operations.
Please trim or change those queries according to the community's style.
*Patch against CVS HEAD.
Thanks
koju
This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinary user of the email address to which it was addressed and may contain copyright and/or legally privileged information. No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive this email in error, please return to sender. Thank you.
If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please email unsubscribe@fast.fujitsu.com.au
Attachments:
temporaryview3.patchapplication/octet-stream; name=temporaryview3.patchDownload
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 ****
<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: /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 ****
<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: /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 <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;
}
;
-
/*****************************************************************************
*
--- 4087,4119 ----
/*****************************************************************************
*
* 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;
}
;
/*****************************************************************************
*
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;
Re: patch for temporary view from TODO list
This has been saved for the 8.1 release:
http:/momjian.postgresql.org/cgi-bin/pgpatches2
---------------------------------------------------------------------------
Koju Iijima wrote:
Dear community,
I have implemented temporary view related items as I proposed few days ago.
http://archives.postgresql.org/pgsql-hackers/2004-09/msg00682.phpAbout test queries for RT (create_view.sql), all queries I added were to
find any errors that may be caused by temporary view related operations.
Please trim or change those queries according to the community's style.*Patch against CVS HEAD.
Thanks
koju
This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinary user of the email address to which it was addressed and may contain copyright and/or legally privileged information. No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive this email in error, please return to sender. Thank you.
If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please email unsubscribe@fast.fujitsu.com.au
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Re: patch for temporary view from TODO list
On Mon, 2004-09-27 at 09:39 +1000, Koju Iijima wrote:
I have implemented temporary view related items as I proposed few days ago.
http://archives.postgresql.org/pgsql-hackers/2004-09/msg00682.php
Barring any objections, I'll apply this patch tomorrow.
-Neil
Re: patch for temporary view from TODO list
Neil Conway <neilc@samurai.com> writes:
On Mon, 2004-09-27 at 09:39 +1000, Koju Iijima wrote:
I have implemented temporary view related items as I proposed few days ago.
http://archives.postgresql.org/pgsql-hackers/2004-09/msg00682.php
Barring any objections, I'll apply this patch tomorrow.
Some minor quibbles:
The references to "CASCADED" (sic) in the patch are surely bogus.
"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.
"explicitely" and "implicitely" are not wel speled.
+ ereport(NOTICE,
+ (errmsg("view \"%s\" will be created in a temporary schema",
+ view->relname)));This seems to me to be exposing an implementation issue without need,
ie, the average user does not care that we use temp schemas to implement
temp tables. Why not
+ (errmsg("view \"%s\" will be a temporary view",
(Possibly the documentation patches should be adjusted similarly.)
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.
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.
[ 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... ]
regards, tom lane
Re: patch for temporary view from TODO list
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
Attachments:
temporaryview4.patchtext/x-patch; charset=ISO-8859-1; name=temporaryview4.patchDownload
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;
Re: patch for temporary view from TODO list
Neil Conway <neilc@samurai.com> writes:
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".
Maybe I'm reading the wrong thing, but the only uses of CASCADED-with-
a-D that I see in the spec are in the context of WITH CHECK OPTION,
which this patch does not implement. I am not sure why we are
documenting stuff we don't implement.
"tempViewWalker" in view.c is bereft of either comments or a usefully
descriptive 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?
isViewOnTempTable_walker. If that isn't euphonious to you, then change
the name of isViewOnTempTable. Everywhere else that we have walker
subroutines, foo() invokes foo_walker(). You need a seriously good reason
to deviate from that convention.
Does the gram.y change really require breaking out OR REPLACE as a
separate production, and if so why?
Without a separate production, you get 8 shift/reduce conflicts because
both opt_or_replace and OptTemp can be reduced on the empty string.
[ scratches head... ] Seems like it ought to work anyway, since there
are no lookahead keywords for which the parse is ambiguous. I still
think there's something odd here.
If we do have to do it this way, a minor readability improvement would
be to write the CREATE case first and CREATE OR REPLACE second.
regards, tom lane
Re: patch for temporary view from TODO list
On Wed, 2005-02-02 at 00:39 -0500, Tom Lane wrote:
Maybe I'm reading the wrong thing, but the only uses of CASCADED-with-
a-D that I see in the spec are in the context of WITH CHECK OPTION,
which this patch does not implement.
Precisely; prior to the patch, WITH CHECK OPTION was documented in the
CREATE VIEW reference page, it was merely done incorrectly ("CASCADE",
not "CASCADED"). This patch corrects that documentation. Whether it is
worth documenting the syntax of unsupported features in the first place
is questionable, but if we're going to do it, we may as well do it
right.
-Neil
Re: patch for temporary view from TODO list
On Wed, 2005-02-02 at 00:39 -0500, Tom Lane wrote:
isViewOnTempTable_walker. If that isn't euphonious to you, then change
the name of isViewOnTempTable. Everywhere else that we have walker
subroutines, foo() invokes foo_walker(). You need a seriously good reason
to deviate from that convention.
[...]
If we do have to do it this way, a minor readability improvement would
be to write the CREATE case first and CREATE OR REPLACE second.
Patch applied to HEAD, including the above suggested changes.
Thanks for the patch, Koju.
-Neil