create schema show_test;
set current_schema to 'show_test';
set b_compatibility_user_host_auth to on;
create user `test_grant`@`127.0.0.1` identified by 'H&*#^DH85@#(J';
create table test(id int);
grant select on table test to 'test_grant'@'127.0.0.1';
grant update on table test to 'test_grant'@'127.0.0.1' with grant option;
grant alter on foreign server log_srv to 'test_grant'@'127.0.0.1';
grant comment on foreign server log_srv to 'test_grant'@'127.0.0.1' with grant option;
grant usage on foreign data wrapper file_fdw to 'test_grant'@'127.0.0.1' with grant option;
/* column */
create table test2(id1 int,id2 int, id3 int);
grant comment(id1, id2) on test2 to 'test_grant'@'127.0.0.1' ;
grant comment(id3) on test2 to 'test_grant'@'127.0.0.1' with grant option;
/* encrypt key */
CREATE CLIENT MASTER KEY proc_cmk2 WITH ( KEY_STORE = localkms , KEY_PATH = "gs_ktool" , ALGORITHM = RSA_3072);
CREATE COLUMN ENCRYPTION KEY proc_cek2 WITH VALUES (CLIENT_MASTER_KEY = proc_cmk2, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);
grant usage on column_encryption_key proc_cek2 to 'test_grant'@'127.0.0.1' with grant option;
grant usage on client_master_key proc_cmk2 to 'test_grant'@'127.0.0.1';
create or replace procedure proc1()
as
declare
genre_rec record; --声明记录类型
begin
for genre_rec in (select e1.ename from public.emp1 e1 join public.emp1mot e1m on e1.mgr = e1m.mgr)
loop
raise notice '%', genre_rec."ename"; --打印
end loop;
end;
/
grant alter on procedure proc1() to 'test_grant'@'127.0.0.1';
grant comment on procedure proc1() to 'test_grant'@'127.0.0.1' with grant option;
CREATE FUNCTION func_add_sql(integer, integer)
RETURNS integer AS 'select $1 + $2;'
LANGUAGE SQL IMMUTABLE
RETURNS NULL ON NULL INPUT;
grant alter on function func_add_sql(integer, integer) to 'test_grant'@'127.0.0.1';
grant comment on function func_add_sql(integer, integer) to 'test_grant'@'127.0.0.1' with grant option;
grant update any table to 'test_grant'@'127.0.0.1';
grant select any table to 'test_grant'@'127.0.0.1' with admin option;
alter user 'test_grant'@'127.0.0.1' with createdb;
show grants for 'test_grant'@'127.0.0.1';
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
(17 rows)
show grants for `test_grant`@`127.0.0.1`;
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
(17 rows)
alter user 'test_grant'@'127.0.0.1' with monadmin;
/* trigger */
CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);
CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS $$ DECLARE BEGIN INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3); RETURN NEW; END $$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION tri_update_func() RETURNS TRIGGER AS $$ DECLARE BEGIN UPDATE test_trigger_des_tbl SET id3 = NEW.id3 WHERE id1=OLD.id1; RETURN OLD; END $$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS $$ DECLARE BEGIN DELETE FROM test_trigger_des_tbl WHERE id1=OLD.id1; RETURN OLD; END $$ LANGUAGE PLPGSQL;
create or replace function tri_truncate_func0010() returns trigger as $$ declare begin truncate table test_trigger_src_tbl; return new; end $$ language plpgsql;
CREATE TRIGGER insert_trigger BEFORE INSERT ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_insert_func();
CREATE TRIGGER update_trigger AFTER UPDATE ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_update_func();
CREATE TRIGGER delete_trigger BEFORE DELETE ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_delete_func();
create trigger truncate_trigger0010 before truncate on test_trigger_src_tbl for each statement execute procedure tri_truncate_func0010();
set dolphin.lower_case_table_names = 0;
set dolphin.sql_mode='sql_mode_full_group';
show dolphin.sql_mode;
dolphin.sql_mode
---------------------
sql_mode_full_group
(1 row)
show function status like 'func_add_s%';
--?.*
--?.*
--?.*
(1 row)
show dolphin.sql_mode;
dolphin.sql_mode
---------------------
sql_mode_full_group
(1 row)
show procedure status like 'proc%';
--?.*
--?.*
--?.*
(1 row)
show dolphin.sql_mode;
dolphin.sql_mode
---------------------
sql_mode_full_group
(1 row)
show triggers in show_test;
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
(4 rows)
show dolphin.sql_mode;
dolphin.sql_mode
---------------------
sql_mode_full_group
(1 row)
reset dolphin.sql_mode;
reset dolphin.lower_case_table_names;
/* show character set */
SHOW CHARSET;
charset | description | default collation | maxlen
--------------+-------------------------------------------------------------+--------------------+--------
abc | alias for WIN1258 | | 1
alt | alias for WIN866 | | 1
big5 | Big Five | | 2
euc_cn | Extended UNIX Code-CN | | 3
eucjis2004 | Extended UNIX Code-JP, JIS X 0213 | | 3
eucjp | Extended UNIX Code-JP, JIS X 0213 | | 3
euckr | Extended UNIX Code-KR | | 3
euctw | Extended UNIX Code-Taiwan, China | | 3
gb18030 | National Standard | gb18030_chinese_ci | 4
gbk | Extended National Standard | gbk_chinese_ci | 2
iso88591 | ISO 8859-1, ECMA 94 | | 1
iso885910 | ISO 8859-10, ECMA 144 | | 1
iso885913 | ISO 8859-13 | | 1
iso885914 | ISO 8859-14 | | 1
iso885915 | ISO 8859-15 | | 1
iso885916 | ISO 8859-16, ASRO SR 14111 | | 1
iso88592 | ISO 8859-2, ECMA 94 | | 1
iso88593 | ISO 8859-3, ECMA 94 | | 1
iso88594 | ISO 8859-4, ECMA 94 | | 1
iso88595 | ISO 8859-5, ECMA 113 | | 1
iso88596 | ISO 8859-6, ECMA 114 | | 1
iso88597 | ISO 8859-7, ECMA 118 | | 1
iso88598 | ISO 8859-8, ECMA 121 | | 1
iso88599 | ISO 8859-9, ECMA 128 | | 1
johab | JOHAB; Extended Unix Code for simplified Chinese | | 3
koi8 | _dirty_ alias for KOI8-R (backward compatibility) | | 1
koi8r | KOI8-R; RFC1489 | | 1
koi8u | KOI8-U; RFC2319 | | 1
latin1 | alias for ISO-8859-1 | | 1
latin10 | alias for ISO-8859-16 | | 1
latin2 | alias for ISO-8859-2 | | 1
latin3 | alias for ISO-8859-3 | | 1
latin4 | alias for ISO-8859-4 | | 1
latin5 | alias for ISO-8859-9 | | 1
latin6 | alias for ISO-8859-10 | | 1
latin7 | alias for ISO-8859-13 | | 1
latin8 | alias for ISO-8859-14 | | 1
latin9 | alias for ISO-8859-15 | | 1
mskanji | alias for Shift_JIS | | 2
muleinternal | Mule internal code | | 4
shiftjis | Shift JIS | | 2
shiftjis2004 | SHIFT-JIS-2004; Shift JIS for Japanese, standard JIS X 0213 | | 2
sjis | alias for Shift_JIS | | 2
sqlascii | Unspecified (see the text) | | 1
tcvn | alias for WIN1258 | | 1
tcvn5712 | alias for WIN1258 | | 1
uhc | Unified Hangul Code | | 2
unicode | alias for UTF8 | | 4
utf8 | Unicode, 8-bit | utf8_general_ci | 4
utf8mb4 | Unicode, 8-bit, aliases for utf8 | utf8mb4_general_ci | 4
vscii | alias for WIN1258 | | 4
win | _dirty_ alias for windows-1251 (backward compatibility) | | 1
win1250 | alias for Windows-1250 | | 1
win1251 | alias for Windows-1251 | | 1
win1252 | alias for Windows-1252 | | 1
win1253 | alias for Windows-1253 | | 1
win1254 | alias for Windows-1254 | | 1
win1255 | alias for Windows-1255 | | 1
win1256 | alias for Windows-1256 | | 1
win1257 | alias for Windows-1257 | | 1
win1258 | alias for Windows-1258 | | 1
win866 | Windows CP866 | | 1
win874 | alias for Windows-874 | | 1
win932 | alias for Shift_JIS | | 2
win936 | alias for GBK | | 2
win949 | alias for UHC | | 2
win950 | alias for BIG5 | | 2
windows1250 | Windows CP1250 | | 1
windows1251 | Windows CP1251 | | 1
windows1252 | Windows CP1252 | | 1
windows1253 | Windows CP1253 | | 1
windows1254 | Windows CP1254 | | 1
windows1255 | Windows CP1255 | | 1
windows1256 | Windows CP1256 | | 1
windows1257 | Windows CP1257 | | 1
windows1258 | Windows CP1258 | | 1
windows866 | Windows CP866 | | 1
windows874 | Windows CP874 | | 1
windows932 | alias for Shift_JIS | | 2
windows936 | alias for GBK | | 2
windows949 | alias for UHC | | 2
windows950 | alias for BIG5 | | 2
(82 rows)
SHOW CHARACTER SET LIKE 'a%';
charset | description | default collation | maxlen
---------+-------------------+-------------------+--------
abc | alias for WIN1258 | | 1
alt | alias for WIN866 | | 1
(2 rows)
SHOW CHARACTER SET WHERE charset LIKE 'i%';
charset | description | default collation | maxlen
-----------+----------------------------+-------------------+--------
iso88591 | ISO 8859-1, ECMA 94 | | 1
iso885910 | ISO 8859-10, ECMA 144 | | 1
iso885913 | ISO 8859-13 | | 1
iso885914 | ISO 8859-14 | | 1
iso885915 | ISO 8859-15 | | 1
iso885916 | ISO 8859-16, ASRO SR 14111 | | 1
iso88592 | ISO 8859-2, ECMA 94 | | 1
iso88593 | ISO 8859-3, ECMA 94 | | 1
iso88594 | ISO 8859-4, ECMA 94 | | 1
iso88595 | ISO 8859-5, ECMA 113 | | 1
iso88596 | ISO 8859-6, ECMA 114 | | 1
iso88597 | ISO 8859-7, ECMA 118 | | 1
iso88598 | ISO 8859-8, ECMA 121 | | 1
iso88599 | ISO 8859-9, ECMA 128 | | 1
(14 rows)
/* show collation */
SHOW COLLATION LIKE 'aa%';
collation | charset | id | default | compiled | sortlen
------------------+---------+-------+---------+----------+---------
--? aa_DJ | utf8 | .* | | Yes |
--? aa_DJ | latin1 | .* | | Yes |
--? aa_DJ.iso88591 | latin1 | .* | | Yes |
--? aa_DJ.utf8 | utf8 | .* | | Yes |
--? aa_ER | utf8 | .* | | Yes |
--? aa_ER.utf8 | utf8 | .* | | Yes |
--? aa_ER.utf8@saaho | utf8 | .* | | Yes |
--? aa_ER@saaho | utf8 | .* | | Yes |
--? aa_ET | utf8 | .* | | Yes |
--? aa_ET.utf8 | utf8 | .* | | Yes |
(10 rows)
SHOW COLLATION WHERE charset = 'win1251';
collation | charset | id | default | compiled | sortlen
--------------+---------+-------+---------+----------+---------
--? be_BY | win1251 | .* | | Yes |
--? be_BY.cp1251 | win1251 | .* | | Yes |
--? bg_BG | win1251 | .* | | Yes |
--? bg_BG.cp1251 | win1251 | .* | | Yes |
(4 rows)
reset search_path;
drop user `test_grant`@`127.0.0.1` cascade;
drop schema show_test cascade;
NOTICE: drop cascades to 12 other objects
DETAIL: drop cascades to table show_test.test
drop cascades to table show_test.test2
drop cascades to client master key: proc_cmk2
drop cascades to column encryption key: proc_cek2
drop cascades to function show_test.proc1()
drop cascades to function show_test.func_add_sql(integer,integer)
drop cascades to table show_test.test_trigger_src_tbl
drop cascades to table show_test.test_trigger_des_tbl
drop cascades to function show_test.tri_insert_func()
drop cascades to function show_test.tri_update_func()
drop cascades to function show_test.tri_delete_func()
drop cascades to function show_test.tri_truncate_func0010()
reset current_schema;
reset b_compatibility_user_host_auth;
