GreenPlum中日常SQL腳本
發(fā)表時(shí)間:2023-07-19 來源:明輝站整理相關(guān)軟件相關(guān)文章人氣:
[摘要]--1.指定月份總共多少天SELECT EXTRACT(DAY FROM CAST(2017-08-01 AS DATE) + INTERVAL 1 month - INTERVAL 1 day)S...
--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.修改默認(rèn)搜索(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.授予指定用戶查詢或所有權(quán)限
GRANT SELECT ON TABLE your_table_name TO other_user;
GRANT ALL ON TABLE your_table_name TO other_user;
--6.授予指定用戶schema使用權(quán)限
GRANT USAGE ON SCHEMA your_schema_name TO other_user;
--7.回收指定用戶schema使用權(quán)限
REVOKE ALL ON SCHEMA your_schema_name FROM other_user;
--不同對(duì)象授權(quán)關(guān)鍵字
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.批量授予函數(shù)執(zhí)行權(quán)限
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
以上就是GreenPlum中日常SQL腳本的詳細(xì)內(nèi)容,更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章!
學(xué)習(xí)教程快速掌握從入門到精通的SQL知識(shí)。