DROP DATABASE IF EXISTS test_db;
NOTICE:  database "test_db" does not exist, skipping
CREATE DATABASE test_db DBCOMPATIBILITY 'B';
CREATE USER "Root_Test" WITH PASSWORD 'openGauss@123';
GRANT ALL PRIVILEGES TO "Root_Test";
\c test_db
SET ROLE "Root_Test" PASSWORD 'openGauss@123';
CREATE USER "Test_User" with password 'openGauss@123';
CREATE VIEW definer_test_view1 AS SELECT log(10, 100);
CREATE DEFINER="Test_User" VIEW definer_test_view2 AS SELECT log(10, 100);
ERROR:  permission denied for schema public
DETAIL:  N/A
CREATE OR REPLACE DEFINER="Test_User" VIEW definer_test_view3 AS SELECT log(10, 100);
ERROR:  permission denied for schema public
DETAIL:  N/A
GRANT ALL PRIVILEGES TO "Test_User";
CREATE DEFINER="Test_User" VIEW definer_test_view2 AS SELECT log(10, 100);
CREATE OR REPLACE DEFINER="Test_User" VIEW definer_test_view3 AS SELECT log(10, 100);
SELECT c.relname as view_name, u.usename as rolname FROM pg_class c, pg_user u WHERE u.usesysid = c.relowner AND relname like '%definer_test_view%' order by view_name;
     view_name      |  rolname  
--------------------+-----------
 definer_test_view1 | Root_Test
 definer_test_view2 | Test_User
 definer_test_view3 | Test_User
(3 rows)

CREATE USER test_user2 with password 'openGauss@123';
SELECT * FROM definer_test_view1;
        log         
--------------------
 2.0000000000000000
(1 row)

ALTER VIEW definer_test_view1 AS SELECT log(10, 1000);
SELECT * FROM definer_test_view1;
        log         
--------------------
 3.0000000000000000
(1 row)

ALTER DEFINER=test_user2 VIEW definer_test_view2 AS SELECT log(10, 100);
create table tab_1107262(id int,c1 int);
create or replace procedure pro_1107262(n int)
as
begin
repeat
insert into tab_1107262 values(n,n*2);
n:=n+1;
until n>10 end repeat;
end;
/
SELECT c.relname as view_name, u.usename as rolname FROM pg_class c, pg_user u WHERE u.usesysid = c.relowner AND relname like '%definer_test_view%' order by view_name;
     view_name      |  rolname   
--------------------+------------
 definer_test_view1 | Root_Test
 definer_test_view2 | test_user2
 definer_test_view3 | Test_User
(3 rows)

-- dump all views
\! @abs_bindir@/gs_dump test_db -p @portstring@ --include-depend-objs --exclude-self --disable-progress | grep -vE '^SET|^REVOKE|^GRANT|^--|^gs_dump|^COMMENT|^DROP'| tr -s '\n' > @abs_bindir@/definer_view_dump.sql 2>&1
\! cat @abs_bindir@/definer_view_dump.sql

CREATE SCHEMA "Test_User";
ALTER SCHEMA "Test_User" OWNER TO "Test_User";
CREATE SCHEMA test_user2;
ALTER SCHEMA test_user2 OWNER TO test_user2;
CREATE DEFINER = "Root_Test" PROCEDURE pro_1107262(n int)  NOT SHIPPABLE
 AS  DECLARE begin
repeat
insert into tab_1107262 values(n,n*2);
n:=n+1;
until n>10 end repeat;
end;
/
ALTER PROCEDURE public.pro_1107262(n integer) OWNER TO "Root_Test";
CREATE VIEW definer_test_view1(log) AS
    SELECT log((10)::numeric, (1000)::numeric) AS log;
ALTER VIEW public.definer_test_view1 OWNER TO "Root_Test";
CREATE VIEW definer_test_view2(log) AS
    SELECT log((10)::numeric, (100)::numeric) AS log;
ALTER VIEW public.definer_test_view2 OWNER TO test_user2;
CREATE VIEW definer_test_view3(log) AS
    SELECT log((10)::numeric, (100)::numeric) AS log;
ALTER VIEW public.definer_test_view3 OWNER TO "Test_User";
CREATE TABLE tab_1107262 (
    id integer,
    c1 integer
)
WITH (orientation=row, compression=no);
ALTER TABLE public.tab_1107262 OWNER TO "Root_Test";
COPY public.tab_1107262 (id, c1) FROM stdin;
\.
;
\! @abs_bindir@/gs_dump test_db -p @portstring@ -F c -f @abs_bindir@/definer_view_dump.dmp --disable-progress
--?gs_dump[port='@portstring@'][test_db].*
--?gs_dump[port='@portstring@'][test_db].*
--?gs_dump[port='@portstring@'][test_db].*
--?gs_dump[port='@portstring@'][test_db].*
--?gs_dump[port='@portstring@'][test_db].*
--?gs_dump[port='@portstring@'][test_db].*
CREATE DATABASE target DBCOMPATIBILITY 'B';
\! @abs_bindir@/gs_restore -d target -p @portstring@ @abs_bindir@/definer_view_dump.dmp --disable-progress
start restore operation ...
end restore operation ...
restore operation successful
--?.*
SELECT * FROM definer_test_view1;
        log         
--------------------
 3.0000000000000000
(1 row)

SELECT * FROM definer_test_view2;
        log         
--------------------
 2.0000000000000000
(1 row)

SELECT * FROM definer_test_view3;
        log         
--------------------
 2.0000000000000000
(1 row)

SELECT c.relname as view_name, u.usename as rolname FROM pg_class c, pg_user u WHERE u.usesysid = c.relowner AND relname like '%definer_test_view%' order by view_name;
     view_name      |  rolname   
--------------------+------------
 definer_test_view1 | Root_Test
 definer_test_view2 | test_user2
 definer_test_view3 | Test_User
(3 rows)

RESET ROLE;
create database test_1;
\c test_1
create table startwith_t(id int, level int, connect_by_isleaf int, connect_by_iscycle int);
create view startwith_v as select id, connect_by_isleaf as level, level as connect_by_isleaf from startwith_t;
\! @abs_bindir@/gs_dump test_1 -p @portstring@ -f @abs_bindir@/dump_postgres.sql --disable-progress
--?gs_dump[port='@portstring@'][test_1].*
--?gs_dump[port='@portstring@'][test_1].*
--?gs_dump[port='@portstring@'][test_1].*
--?gs_dump[port='@portstring@'][test_1].*
--?gs_dump[port='@portstring@'][test_1].*
--?gs_dump[port='@portstring@'][test_1].*
drop database if exists test_2;
NOTICE:  database "test_2" does not exist, skipping
create database test_2;
\! @abs_bindir@/gsql -d test_2 -p @portstring@ -f @abs_bindir@/dump_postgres.sql
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE VIEW
ALTER VIEW
REVOKE
REVOKE
GRANT
GRANT
--?.*
\! @abs_bindir@/gsql -d test_2 -p @portstring@ -c "select * from startwith_v;"
 id | level | connect_by_isleaf 
----+-------+-------------------
(0 rows)

\c regression
drop database test_db;
drop database test_1;
drop database test_2;
drop database target;
DROP USER "Test_User";
DROP USER test_user2;
DROP USER "Root_Test" CASCADE;