GreenPlum之日常SQL脚本笔记(一)

--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)路径

SHOW search_path;SET search_path TO your_schema_name;

--3.更改schema所有者

ALTER SCHEMA your_schema_name OWNER TO other_user;

--4.修改表的所有者

ALTER TABLE your_table_name OWNER TO other_user;

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

GRANT SELECT ON TABLE your_table_name TO other_user;GRANT ALL ON TABLE your_table_name TO other_user;

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

GRANT USAGE ON SCHEMA your_schema_name TO other_user;

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

REVOKE ALL ON SCHEMA your_schema_name FROM other_user;

--不同对象授权关键字

TABLES,VIEWS,SEQUENCES: SELECT INSERT UPDATE DELETE RULE ALLEXTERNAL TABLES: SELECT RULE ALLDATABASES: CONNECT CREATE TEMPORARY|TEMP ALLFUNCTIONS: EXECUTEPROCEDURAL LANGUAGES: USAGESCHEMAS: 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;' batchgrantFROM information_schema.routinesJOIN information_schema.parameters ON routines.specific_name=parameters.specific_nameWHERE routines.specific_schema='product'GROUP BY routines.routine_name