GreenPlum中日常SQL脚本-木庄网络博客

本文摘自PHP中文网，作者一个新手，侵删。

--1.指定月份总共多少天

SELECT EXTRACT(DAY FROM CAST('2017-08-01' AS DATE) + INTERVAL '1 month' - INTERVAL '1 day')
 
SELECT EXTRACT(DAY FROM CAST(TO_CHAR(NOW(),'YYYY-MM')||'-01' AS DATE) + INTERVAL '1 month' - INTERVAL '1 day')

--2.修改默认搜索(search_path)路径

1 2	`SHOW search_path;` `SET` `search_path` `TO` `your_schema_name;`

--3.更改schema所有者

1	`ALTER` `SCHEMA` `your_schema_name OWNER` `TO` `other_user;`

--4.修改表的所有者

1	`ALTER` `TABLE` `your_table_name OWNER` `TO` `other_user;`

--5.授予指定用户查询或所有权限

1 2	`GRANT` `SELECT` `ON` `TABLE` `your_table_name` `TO` `other_user;` `GRANT` `ALL` `ON` `TABLE` `your_table_name` `TO` `other_user;`

--6.授予指定用户schema使用权限

1	`GRANT` `USAGE` `ON` `SCHEMA` `your_schema_name` `TO` `other_user;`

--7.回收指定用户schema使用权限

1	`REVOKE` `ALL` `ON` `SCHEMA` `your_schema_name` `FROM` `other_user;`

--不同对象授权关键字

TABLES,VIEWS,SEQUENCES: SELECT INSERT UPDATE DELETE RULE ALL
EXTERNAL TABLES: SELECT RULE ALL
DATABASES: CONNECT CREATE TEMPORARY|TEMP ALL
FUNCTIONS: EXECUTE
PROCEDURAL LANGUAGES: USAGE
SCHEMAS: CREATE USAGE ALL

--8.批量授予函数执行权限

SELECT 'GRANT EXECUTE ON FUNCTION '||routines.routine_name||'('||STRING_AGG(parameters.data_type,',' ORDER BY parameters.ordinal_position)||') TO other_user;' batchgrant
FROM information_schema.routines
JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name
WHERE routines.specific_schema='product'
GROUP BY routines.routine_name