欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

PostgreSQL批量修改函數(shù)擁有者的操作

 更新時間:2021年01月04日 11:10:42   作者:modestchen  
這篇文章主要介紹了PostgreSQL批量修改函數(shù)擁有者的操作,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧

Postgresql如何批量修改函數(shù)擁有者,默認創(chuàng)建的函數(shù)對象的擁有者為當前創(chuàng)建的用戶,如果使用postgres超級管理員創(chuàng)建一個test()的函數(shù),擁有者就是postgres用戶。下面講解下如何批量修改擁有者。

本文演示的Postgresql版本如下:

PostgreSQL 9.6.8

相關(guān)視圖

要查詢Postgresql的函數(shù)和函數(shù)參數(shù)需要使用函數(shù)視圖和參數(shù)視圖,分別記錄了函數(shù)信息和參數(shù)列表信息。

視圖一: information_schema.routines

視圖routines包含當前數(shù)據(jù)庫中所有的函數(shù)。只有那些當前用戶能夠訪問(作為擁有者或具有某些特權(quán))的函數(shù)才會被顯示。需要用到的列如下,完整視圖講解請參考官方文檔。

名稱 數(shù)據(jù)類型 備注
specific_schema sql_identifier 包含該函數(shù)的模式名
routine_name sql_identifier 該函數(shù)的名字(在重載的情況下可能重復)
specific_name sql_identifier 該函數(shù)的"專用名"。這是一個在模式中唯一標識該函數(shù)的名稱,即使該函數(shù)真正的名稱已經(jīng)被重載。專用名的格式尚未被定義,它應當僅被用來與指定例程名稱的其他實例進行比較。

視圖二: information_schema.parameters

視圖parameters包含當前數(shù)據(jù)庫中所有函數(shù)的參數(shù)的有關(guān)信息。只有那些當前用戶能夠訪問(作為擁有者或具有某些特權(quán))的函數(shù)才會被顯示。需要用到的列如下,完整視圖講解請參考官方文檔。

名稱 數(shù)據(jù)類型 備注
parameter_name sql_identifier 參數(shù)名,如果參數(shù)沒有名稱則為空
udt_name sql_identifier 該參數(shù)的數(shù)據(jù)類型的名字
ordinal_position cardinal_number 該參數(shù)在函數(shù)參數(shù)列表中的順序位置(從 1 開始計數(shù))
specific_name cardinal_number 該函數(shù)的"專用名"。詳見第 35.40 節(jié)。

注意:可以通過routines. specific_name 和 parameters.specific_name字段關(guān)聯(lián)查詢。

單個修改

如果需要修改的函數(shù)只有一個,請執(zhí)行如下SQL語句即可:

如果需要修改的函數(shù)只有一個,請執(zhí)行如下SQL語句即可:

// 無參數(shù)函數(shù)
ALTER FUNCTION "abc"."test"() OWNER TO "dbadmin";
//帶參數(shù)函數(shù)
ALTER FUNCTION "abc"."test3"(p1 varchar, p2 varchar) OWNER TO "dbadmin";

批量修改

首先可以查詢當前模式下函數(shù)的所有者分別是哪個用戶,使用下面SQL來查詢:

SELECT 
n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_function_result(p.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
pg_catalog.pg_get_userbyid(p.proowner) as "Owner"
FROM pg_catalog.pg_proc p
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
  LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
WHERE pg_catalog.pg_function_is_visible(p.oid)
  AND n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
ORDER BY 1, 2;

當前顯示模式“abc”有2個無參函數(shù)和1個帶參函數(shù),擁有者都是postgres超級用戶。

然后根據(jù)上面講的兩個視圖: routines 和 parameters關(guān)聯(lián)查詢出模式下的所有函數(shù)和參數(shù)(目的是為了拼接SQL語句),參考如下:

SELECT 
"routines".specific_schema,
"routines".routine_name, 
COALESCE("parameters".parameter_name, '') AS parameter_name, -- COALESCE返回參數(shù)中的第一個非null的值
COALESCE("parameters".udt_name, '') AS udt_name,
COALESCE("parameters".parameter_name, '') || ' ' || COALESCE("parameters".udt_name, '') AS params,
"parameters".ordinal_position 
FROM "information_schema"."routines"
LEFT JOIN "information_schema"."parameters" ON "routines".specific_name="parameters".specific_name 
WHERE "routines".specific_schema='abc' 
ORDER BY 1,2,6;

這里我們再使用聚合函數(shù): string_agg 把字段 params所有行連接成字符串,并用逗號分隔符分隔。

WITH tmp AS (SELECT 
"routines".specific_schema,
"routines".routine_name, 
COALESCE("parameters".parameter_name, '') AS parameter_name, -- COALESCE返回參數(shù)中的第一個非null的值
COALESCE("parameters".udt_name, '') AS udt_name,
COALESCE("parameters".parameter_name, '') || ' ' || COALESCE("parameters".udt_name, '') AS params,
"parameters".ordinal_position 
FROM "information_schema"."routines"
LEFT JOIN "information_schema"."parameters" ON "routines".specific_name="parameters".specific_name 
WHERE "routines".specific_schema='abc' 
ORDER BY 1,2,6) 
SELECT 
specific_schema, 
routine_name, 
string_agg(params, ',') AS params, 
'"'||specific_schema||'"."'||routine_name||'"('||string_agg(params, ',')||')' AS fname 
FROM tmp GROUP BY specific_schema, routine_name; 

最后使用一個Postgres執(zhí)行代碼片段完成批量修改,完整SQL如下:

DO $$
DECLARE r record;
BEGIN
FOR r IN
WITH tmp AS (SELECT 
"routines".specific_schema,
"routines".routine_name, 
COALESCE("parameters".parameter_name, '') AS parameter_name, -- COALESCE返回參數(shù)中的第一個非null的值
COALESCE("parameters".udt_name, '') AS udt_name,
COALESCE("parameters".parameter_name, '') || ' ' || COALESCE("parameters".udt_name, '') AS params,
"parameters".ordinal_position 
FROM "information_schema"."routines"
LEFT JOIN "information_schema"."parameters" ON "routines".specific_name="parameters".specific_name 
WHERE "routines".specific_schema='abc' 
ORDER BY 1,2,6) SELECT '"'||specific_schema||'"."'||routine_name||'"('||string_agg(params, ',')||')' AS fname FROM tmp GROUP BY specific_schema, routine_name
LOOP
EXECUTE 'ALTER FUNCTION '|| r.fname||' OWNER TO "dbadmin" ';
END LOOP;
END $$;

可以看到模式“abc”的Owner已經(jīng)全部改為dbadmin這個賬號了。

上次批量修改函數(shù)可能存在部分特殊場景會報錯, 會把“參數(shù)類型” + “返回類型” 拼接在一起

改進方法:我們通過pg_catalog目錄來實現(xiàn)批量修改,參考代碼如下:

DO $$
DECLARE r record;
BEGIN
FOR r IN
 WITH tmp AS (
 SELECT n.nspname as "Schema",
 p.proname as "Name",
 pg_catalog.pg_get_function_result(p.oid) as "Result data type",
 pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
 WHEN p.proisagg THEN 'agg'
 WHEN p.proiswindow THEN 'window'
 WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
 ELSE 'normal'
 END as "Type"
 FROM pg_catalog.pg_proc p
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 WHERE n.nspname = 'etl'
 ORDER BY 1, 2, 4
 ) SELECT '"' || "Schema" || '"' || '.' || '"' || "Name" || '"' || '(' || "Argument data types" ||')' AS fname FROM tmp
LOOP
EXECUTE 'ALTER FUNCTION '|| r.fname||' OWNER TO "postgres" ';
END LOOP;
END $$;

補充:PostgreSQL更改Owner所有者

網(wǎng)上一個大神寫的

SELECT
‘a(chǎn)lter table ' || nsp.nspname || ‘.' || cls.relname || ' owner to usr_zhudong;' || chr ( 13 )
FROM
pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp
WHERE
nsp.nspname IN ( ‘public' )
AND cls.relnamespace = nsp.oid
AND cls.relkind = ‘r'
ORDER BY
nsp.nspname,
cls.relname;

我來做一個改版

SELECT
'alter table ' || nsp.nspname || '.' || cls.relname || ' owner to test2;' || chr ( 13 )
FROM
pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp
WHERE
nsp.nspname IN ( 'public' )
AND cls.relnamespace = nsp.oid
AND cls.relkind = 'r'
ORDER BY
nsp.nspname,
cls.relname;
 
SELECT
'alter table "' || nsp.nspname || '"."' || cls.relname || '" owner to user01;' || chr ( 13 )
FROM
pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp
WHERE
nsp.nspname IN ( 'public' )
AND cls.relnamespace = nsp.oid
AND cls.relkind = 'r'
ORDER BY
nsp.nspname,
cls.relname;

效果:

以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方,望不吝賜教。

相關(guān)文章

  • PostgreSQL截取字符串到指定字符位置詳細示例

    PostgreSQL截取字符串到指定字符位置詳細示例

    這篇文章主要給大家介紹了關(guān)于PostgreSQL截取字符串到指定字符位置的相關(guān)資料,PostgreSQL數(shù)據(jù)庫拼接字符串函數(shù)是一種非常重要的函數(shù),使用它可以方便地將不同的字符串進行拼接操作,從而得到我們需要的結(jié)果,需要的朋友可以參考下
    2023-07-07
  • 將PostgreSQL的數(shù)據(jù)實時同步到Doris的技巧分享

    將PostgreSQL的數(shù)據(jù)實時同步到Doris的技巧分享

    眾所周知,在兩個毫不相干的數(shù)據(jù)管理系統(tǒng)之間進行數(shù)據(jù)同步,特別是實時同步,其復雜程度足以讓高級DBA腦瓜疼,本文給大家介紹了將PostgreSQL的數(shù)據(jù)實時同步到Doris的技巧分享,需要的朋友可以參考下
    2024-03-03
  • PostgreSQL 重復數(shù)據(jù)處理的操作方法

    PostgreSQL 重復數(shù)據(jù)處理的操作方法

    這篇文章主要介紹了PostgreSQL 重復數(shù)據(jù)處理的操作方法,本文給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下
    2020-12-12
  • Postgresql ALTER語句常用操作小結(jié)

    Postgresql ALTER語句常用操作小結(jié)

    這篇文章主要介紹了Postgresql ALTER語句常用操作小結(jié),本文講解了增加一列、刪除一列、更改列的數(shù)據(jù)類型、表的重命名、更改列的名字、字段的not null設置等常用操作的代碼示例,需要的朋友可以參考下
    2015-06-06
  • postgres主備切換之文件觸發(fā)方式詳解

    postgres主備切換之文件觸發(fā)方式詳解

    這篇文章主要介紹了postgres主備切換之文件觸發(fā)方式詳解,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2020-12-12
  • PostgreSQL常用字符串分割函數(shù)整理匯總

    PostgreSQL常用字符串分割函數(shù)整理匯總

    作為當前最強大的開源數(shù)據(jù)庫,Postgresql(以下簡稱pg)對字符的處理也是最為強大的,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL常用字符串分割函數(shù)的相關(guān)資料,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下
    2022-07-07
  • PostgreSQL教程(十九):SQL語言函數(shù)

    PostgreSQL教程(十九):SQL語言函數(shù)

    這篇文章主要介紹了PostgreSQL教程(十九):SQL語言函數(shù),本文講解了SQL語言函數(shù)基本概念、基本類型、復合類型、帶輸出參數(shù)的函數(shù)、返回結(jié)果作為表數(shù)據(jù)源等內(nèi)容,需要的朋友可以參考下
    2015-05-05
  • postgresql常用日期函數(shù)使用整理

    postgresql常用日期函數(shù)使用整理

    在開發(fā)過程中經(jīng)常要取日期的年,月,日,小時等值,下面這篇文章主要給大家介紹了關(guān)于postgresql常用日期函數(shù)使用整理的相關(guān)資料,文中通過代碼及圖文介紹的非常詳細,需要的朋友可以參考下
    2024-02-02
  • PostgreSQL處理數(shù)據(jù)并發(fā)更新沖突的解決方法

    PostgreSQL處理數(shù)據(jù)并發(fā)更新沖突的解決方法

    在數(shù)據(jù)庫并發(fā)操作環(huán)境中,多個事務同時嘗試更新相同的數(shù)據(jù)可能導致沖突,PostgreSQL?提供了一系列機制來處理這些并發(fā)更新沖突,以確保數(shù)據(jù)的一致性和完整性,所以本文給大家介紹了PostgreSQL處理數(shù)據(jù)并發(fā)更新沖突的解決方法,需要的朋友可以參考下
    2024-07-07
  • PostgreSQL HOT與PHOT有哪些區(qū)別

    PostgreSQL HOT與PHOT有哪些區(qū)別

    這篇文章主要介紹了PostgreSQL8.3版本開始就引入的HOT機制與PHOT使用區(qū)別,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
    2022-09-09

最新評論