create schema test_dcl_func;
set current_schema to 'test_dcl_func';
create table table1(a int);
insert into table1 values (1),(2);
-- save point and roll back
START TRANSACTION;
INSERT INTO table1 VALUES (3),(4);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3),(4);
SAVEPOINT my_savepoint2;
ROLLBACK TO SAVEPOINT my_savepoint;
ROLLBACK TO SAVEPOINT my_savepoint2;
ERROR:  no such savepoint
end;
START TRANSACTION;
INSERT INTO table1 VALUES (3),(4);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3),(4);
SAVEPOINT my_savepoint;
ROLLBACK TO SAVEPOINT my_savepoint;
ROLLBACK TO SAVEPOINT my_savepoint;
end;
START TRANSACTION;
INSERT INTO table1 VALUES (3),(4);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3),(4);
SAVEPOINT my_savepoint;
ROLLBACK TO SAVEPOINT my_savepoint3;
ERROR:  no such savepoint
ROLLBACK TO SAVEPOINT my_savepoint3;
ERROR:  no such savepoint
end;
START TRANSACTION;
INSERT INTO table1 VALUES (3),(4);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3),(4);
SAVEPOINT my_savepoint;
ROLLBACK TO SAVEPOINT my_savepoint;
ROLLBACK TO SAVEPOINT my_savepoint;
ROLLBACK work to savepoint my_savepoint;
ROLLBACK to  my_savepoint;
ROLLBACK work to my_savepoint;
end;
create or replace procedure test_cursor_0
as
    a int;
    cursor c1 is SELECT * from table1;
begin
    INSERT INTO table1 VALUES (3),(4);
    SAVEPOINT my_savepoint;
    INSERT INTO table1 VALUES (3),(4);
    SAVEPOINT my_savepoint;
    ROLLBACK TO SAVEPOINT my_savepoint;
    ROLLBACK TO SAVEPOINT my_savepoint;
    ROLLBACK work to savepoint my_savepoint;
    ROLLBACK to  my_savepoint;
    ROLLBACK work to my_savepoint;
end;
/
ERROR:  syntax error at or near "work"
LINE 10:     ROLLBACK work to savepoint my_savepoint;
                      ^
QUERY:   DECLARE a int;
    cursor c1 is SELECT * from table1;
begin
    INSERT INTO table1 VALUES (3),(4);
    SAVEPOINT my_savepoint;
    INSERT INTO table1 VALUES (3),(4);
    SAVEPOINT my_savepoint;
    ROLLBACK TO SAVEPOINT my_savepoint;
    ROLLBACK TO SAVEPOINT my_savepoint;
    ROLLBACK work to savepoint my_savepoint;
    ROLLBACK to  my_savepoint;
    ROLLBACK work to my_savepoint;
end
--- close cursor
START TRANSACTION;
CURSOR cursor1 FOR SELECT * from table1;
CLOSE cursor1;
CLOSE cursor1;
ERROR:  cursor "cursor1" does not exist
end;
START TRANSACTION;
CURSOR cursor1 FOR SELECT * from table1;
commit;
CLOSE cursor1;
ERROR:  cursor "cursor1" does not exist
end;
WARNING:  there is no transaction in progress
START TRANSACTION;
CURSOR cursor1 FOR SELECT * from table1;
CLOSE cursor1;
FETCH FORWARD 2 FROM cursor1;
ERROR:  cursor "cursor1" does not exist
end;
START TRANSACTION;
CURSOR cursor1 FOR SELECT * from table1;
CLOSE ALL;
FETCH FORWARD 2 FROM cursor1;
ERROR:  cursor "cursor1" does not exist
end;
create or replace procedure test_cursor_1
as
    a int;
    cursor c1 is SELECT * from table1;
begin
    OPEN c1;
    close c1;
	fetch c1 into a;
end;
/
call test_cursor_1();
ERROR:  cursor "<unnamed portal 1>" does not exist in FETCH statement.
CONTEXT:  PL/pgSQL function test_cursor_1() line 6 at FETCH
create or replace procedure test_cursor_2
as
    a int;
    cursor c1 is SELECT * from table1;
begin
    OPEN c1;
    close c1;
	open c1;
	fetch c1 into a;
	close c1;
	open c1;
	close c1;
end;
/
call test_cursor_2();
create or replace procedure test_cursor_3
as
    a int;
    cursor c1 is SELECT * from table1;
begin
    OPEN c1;
    close c1;
	close c1;
	open c1;
	fetch c1 into a;
end;
/
call test_cursor_3();
ERROR:  cursor "<unnamed portal 3>" does not exist in CLOSE statement.
CONTEXT:  PL/pgSQL function test_cursor_3() line 6 at CLOSE
create or replace procedure test_cursor_4
as
    a int;
    cursor c1 is SELECT * from table1;
begin
    OPEN c1;
    close all;
	close c1;
	open c1;
	fetch c1 into a;
end;
/
ERROR:  syntax error at or near "all"
LINE 5:     close all;
                  ^
QUERY:   DECLARE a int;
    cursor c1 is SELECT * from table1;
begin
    OPEN c1;
    close all;
	close c1;
	open c1;
	fetch c1 into a;
end
call test_cursor_4();
ERROR:  function "test_cursor_4" doesn't exist 
-- desc
create table test1(id int, id1 int, id2 int, not_id int);
insert into test1 values (1, 2, 3);
create view v1 as select * from test1;
create MATERIALIZED view mv1 as select * from test1;
create index idx1 on test1(id);
desc test1;
 Field  |  Type   | Null | Key | Default | Extra 
--------+---------+------+-----+---------+-------
 id     | integer | YES  | MUL | NULL    | 
 id1    | integer | YES  |     | NULL    | 
 id2    | integer | YES  |     | NULL    | 
 not_id | integer | YES  |     | NULL    | 
(4 rows)

desc test1 'id%';
ERROR:  syntax error at or near "test1"
LINE 1: desc test1 'id%';
             ^
desc test1 'id';
ERROR:  syntax error at or near "test1"
LINE 1: desc test1 'id';
             ^
desc test1 '.%';
ERROR:  syntax error at or near "test1"
LINE 1: desc test1 '.%';
             ^
desc test1 '*%';
ERROR:  syntax error at or near "test1"
LINE 1: desc test1 '*%';
             ^
desc test1 '..%';
ERROR:  syntax error at or near "test1"
LINE 1: desc test1 '..%';
             ^
desc test1 '***%';
ERROR:  syntax error at or near "test1"
LINE 1: desc test1 '***%';
             ^
desc test2;
ERROR:  Table 'test_dcl_func.test2' doesn't exist.
desc 'tes%' '.*%';
ERROR:  syntax error at or near "'tes%'"
LINE 1: desc 'tes%' '.*%';
             ^
desc test1 'id1,id2';
ERROR:  syntax error at or near "test1"
LINE 1: desc test1 'id1,id2';
             ^
desc v1;
 Field  |  Type   | Null | Key | Default | Extra 
--------+---------+------+-----+---------+-------
 id     | integer | YES  |     | NULL    | 
 id1    | integer | YES  |     | NULL    | 
 id2    | integer | YES  |     | NULL    | 
 not_id | integer | YES  |     | NULL    | 
(4 rows)

desc v1 'id%';
ERROR:  syntax error at or near "v1"
LINE 1: desc v1 'id%';
             ^
desc mv1;
 Field  |  Type   | Null | Key | Default | Extra 
--------+---------+------+-----+---------+-------
 id     | integer | YES  |     | NULL    | 
 id1    | integer | YES  |     | NULL    | 
 id2    | integer | YES  |     | NULL    | 
 not_id | integer | YES  |     | NULL    | 
(4 rows)

desc mv1 'id%';
ERROR:  syntax error at or near "mv1"
LINE 1: desc mv1 'id%';
             ^
describe test1;
 Field  |  Type   | Null | Key | Default | Extra 
--------+---------+------+-----+---------+-------
 id     | integer | YES  | MUL | NULL    | 
 id1    | integer | YES  |     | NULL    | 
 id2    | integer | YES  |     | NULL    | 
 not_id | integer | YES  |     | NULL    | 
(4 rows)

describe test1 'id%';
ERROR:  syntax error at or near "test1"
LINE 1: describe test1 'id%';
                 ^
describe test1 'id';
ERROR:  syntax error at or near "test1"
LINE 1: describe test1 'id';
                 ^
describe test1 '.%';
ERROR:  syntax error at or near "test1"
LINE 1: describe test1 '.%';
                 ^
describe test1 '*%';
ERROR:  syntax error at or near "test1"
LINE 1: describe test1 '*%';
                 ^
describe test1 '..%';
ERROR:  syntax error at or near "test1"
LINE 1: describe test1 '..%';
                 ^
describe test1 '***%';
ERROR:  syntax error at or near "test1"
LINE 1: describe test1 '***%';
                 ^
describe test2;
ERROR:  Table 'test_dcl_func.test2' doesn't exist.
describe 'tes%' '.*%';
ERROR:  syntax error at or near "'tes%'"
LINE 1: describe 'tes%' '.*%';
                 ^
describe test1 'id1,id2';
ERROR:  syntax error at or near "test1"
LINE 1: describe test1 'id1,id2';
                 ^
describe v1;
 Field  |  Type   | Null | Key | Default | Extra 
--------+---------+------+-----+---------+-------
 id     | integer | YES  |     | NULL    | 
 id1    | integer | YES  |     | NULL    | 
 id2    | integer | YES  |     | NULL    | 
 not_id | integer | YES  |     | NULL    | 
(4 rows)

describe v1 'id%';
ERROR:  syntax error at or near "v1"
LINE 1: describe v1 'id%';
                 ^
describe mv1;
 Field  |  Type   | Null | Key | Default | Extra 
--------+---------+------+-----+---------+-------
 id     | integer | YES  |     | NULL    | 
 id1    | integer | YES  |     | NULL    | 
 id2    | integer | YES  |     | NULL    | 
 not_id | integer | YES  |     | NULL    | 
(4 rows)

describe mv1 'id%';
ERROR:  syntax error at or near "mv1"
LINE 1: describe mv1 'id%';
                 ^
EXPLAIN test1;
 Field  |  Type   | Null | Key | Default | Extra 
--------+---------+------+-----+---------+-------
 id     | integer | YES  | MUL | NULL    | 
 id1    | integer | YES  |     | NULL    | 
 id2    | integer | YES  |     | NULL    | 
 not_id | integer | YES  |     | NULL    | 
(4 rows)

EXPLAIN test1 'id%';
ERROR:  syntax error at or near "test1"
LINE 1: EXPLAIN test1 'id%';
                ^
EXPLAIN test1 'id';
ERROR:  syntax error at or near "test1"
LINE 1: EXPLAIN test1 'id';
                ^
EXPLAIN test1 '.%';
ERROR:  syntax error at or near "test1"
LINE 1: EXPLAIN test1 '.%';
                ^
EXPLAIN test1 '*%';
ERROR:  syntax error at or near "test1"
LINE 1: EXPLAIN test1 '*%';
                ^
EXPLAIN test1 '..%';
ERROR:  syntax error at or near "test1"
LINE 1: EXPLAIN test1 '..%';
                ^
EXPLAIN test1 '***%';
ERROR:  syntax error at or near "test1"
LINE 1: EXPLAIN test1 '***%';
                ^
EXPLAIN test2;
ERROR:  Table 'test_dcl_func.test2' doesn't exist.
EXPLAIN 'tes%' '.*%';
ERROR:  syntax error at or near "'tes%'"
LINE 1: EXPLAIN 'tes%' '.*%';
                ^
EXPLAIN test1 'id1,id2';
ERROR:  syntax error at or near "test1"
LINE 1: EXPLAIN test1 'id1,id2';
                ^
EXPLAIN v1;
 Field  |  Type   | Null | Key | Default | Extra 
--------+---------+------+-----+---------+-------
 id     | integer | YES  |     | NULL    | 
 id1    | integer | YES  |     | NULL    | 
 id2    | integer | YES  |     | NULL    | 
 not_id | integer | YES  |     | NULL    | 
(4 rows)

EXPLAIN v1 'id%';
ERROR:  syntax error at or near "v1"
LINE 1: EXPLAIN v1 'id%';
                ^
EXPLAIN mv1;
 Field  |  Type   | Null | Key | Default | Extra 
--------+---------+------+-----+---------+-------
 id     | integer | YES  |     | NULL    | 
 id1    | integer | YES  |     | NULL    | 
 id2    | integer | YES  |     | NULL    | 
 not_id | integer | YES  |     | NULL    | 
(4 rows)

EXPLAIN mv1 'id%';
ERROR:  syntax error at or near "mv1"
LINE 1: EXPLAIN mv1 'id%';
                ^
drop view v1;
drop MATERIALIZED view mv1;
drop table test1;
drop table table1;
drop procedure test_cursor_0;
ERROR:  function test_cursor_0 does not exist
drop procedure test_cursor_1;
drop procedure test_cursor_2;
drop procedure test_cursor_3;
drop procedure test_cursor_4;
ERROR:  function test_cursor_4 does not exist
drop schema test_dcl_func cascade;
reset current_schema;