github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/rename_view (about)

     1  statement error pgcode 42P01 relation "foo" does not exist
     2  ALTER VIEW foo RENAME TO bar
     3  
     4  statement ok
     5  ALTER VIEW IF EXISTS foo RENAME TO bar
     6  
     7  statement ok
     8  CREATE TABLE kv (
     9    k INT PRIMARY KEY,
    10    v INT
    11  )
    12  
    13  statement ok
    14  INSERT INTO kv VALUES (1, 2), (3, 4)
    15  
    16  statement ok
    17  CREATE VIEW v as SELECT k,v FROM kv
    18  
    19  query II rowsort
    20  SELECT * FROM v
    21  ----
    22  1 2
    23  3 4
    24  
    25  query TTT
    26  SHOW TABLES
    27  ----
    28  public  kv  table
    29  public  v   view
    30  
    31  statement error pgcode 42809 "kv" is not a view
    32  ALTER VIEW kv RENAME TO new_kv
    33  
    34  # We allow ALTER TABLE for renaming views.
    35  statement ok
    36  ALTER TABLE v RENAME TO new_v
    37  
    38  statement error pgcode 42P01 relation "v" does not exist
    39  SELECT * FROM v
    40  
    41  query II rowsort
    42  SELECT * FROM new_v
    43  ----
    44  1 2
    45  3 4
    46  
    47  query TTT
    48  SHOW TABLES
    49  ----
    50  public  kv     table
    51  public  new_v  view
    52  
    53  # check the name in the descriptor, which is used by SHOW GRANTS, is also changed
    54  query TTTTT
    55  SHOW GRANTS ON new_v
    56  ----
    57  test  public new_v  admin  ALL
    58  test  public new_v  root   ALL
    59  
    60  statement error invalid table name: ""
    61  ALTER VIEW "" RENAME TO foo
    62  
    63  statement error invalid table name: ""
    64  ALTER VIEW new_v RENAME TO ""
    65  
    66  statement ok
    67  ALTER VIEW new_v RENAME TO new_v
    68  
    69  statement ok
    70  CREATE TABLE t (
    71    c1 INT PRIMARY KEY,
    72    c2 INT
    73  )
    74  
    75  statement ok
    76  INSERT INTO t VALUES (4, 16), (5, 25)
    77  
    78  statement ok
    79  CREATE VIEW v as SELECT c1,c2 from t
    80  
    81  statement error pgcode 42P07 relation "new_v" already exists
    82  ALTER VIEW v RENAME TO new_v
    83  
    84  user testuser
    85  
    86  statement error user testuser does not have DROP privilege on relation v
    87  ALTER VIEW test.v RENAME TO v2
    88  
    89  user root
    90  
    91  statement ok
    92  GRANT DROP ON test.v TO testuser
    93  
    94  statement ok
    95  create database test2
    96  
    97  user testuser
    98  
    99  statement error user testuser does not have CREATE privilege on database test
   100  ALTER VIEW test.v RENAME TO v2
   101  
   102  user root
   103  
   104  statement ok
   105  GRANT CREATE ON DATABASE test TO testuser
   106  
   107  statement ok
   108  ALTER VIEW test.v RENAME TO v2
   109  
   110  query TTT
   111  SHOW TABLES FROM test
   112  ----
   113  public  kv     table
   114  public  new_v  view
   115  public  t      table
   116  public  v2     view
   117  
   118  user testuser
   119  
   120  statement error user testuser does not have CREATE privilege on database test2
   121  ALTER VIEW test.v2 RENAME TO test2.v
   122  
   123  user root
   124  
   125  statement ok
   126  GRANT CREATE ON DATABASE test2 TO testuser
   127  
   128  statement ok
   129  GRANT DROP ON test.new_v TO testuser
   130  
   131  user testuser
   132  
   133  statement ok
   134  ALTER VIEW test.new_v RENAME TO test2.v
   135  
   136  statement ok
   137  ALTER VIEW test.v2 RENAME TO test2.v2
   138  
   139  query TTT
   140  SHOW TABLES FROM test
   141  ----
   142  
   143  query TTT
   144  SHOW TABLES FROM test2
   145  ----
   146  public  v   view
   147  public  v2  view
   148  
   149  user root
   150  
   151  query II rowsort
   152  SELECT * FROM test2.v
   153  ----
   154  1 2
   155  3 4
   156  
   157  query II rowsort
   158  SELECT * FROM test2.v2
   159  ----
   160  4 16
   161  5 25
   162  
   163  statement ok
   164  CREATE VIEW v3 AS SELECT count(*) FROM test2.v AS v JOIN test2.v2 AS v2 ON v.k > v2.c1
   165  
   166  statement error cannot rename relation "test2.public.v" because view "test.public.v3" depends on it
   167  ALTER VIEW test2.v RENAME TO test2.v3
   168  
   169  statement error cannot rename relation "test2.public.v2" because view "test.public.v3" depends on it
   170  ALTER VIEW test2.v2 RENAME TO v4
   171  
   172  statement ok
   173  ALTER VIEW v3 RENAME TO v4
   174  
   175  statement error cannot rename relation "test2.public.v2" because view "test.public.v4" depends on it
   176  ALTER VIEW test2.v2 RENAME TO v5