2022-12-01 PostgreSQL DB加密 改变所有表中某个字段的值

/*========================================================================
 ① Sample
 =========================================================================

CREATE OR REPLACE PROCEDURE public.cnamekna()
 LANGUAGE plpgsql
AS $procedure$ DECLARE 
/*------------------------------------------------------------------------------
  定数定義
  */
--
/*------------------------------------------------------------------------------
  変数定義
  */
--
WsSQL varchar(30000); 
--
/*------------------------------------------------------------------------------
  カーソル定義
  */
--
csrXCSQL CURSOR FOR 
select
    table_name AS TABNM
    , column_name AS COLNM
from
    information_schema.columns 
where
    table_name <> 'pg_stat_statements' 
    and table_schema = 'public' 
    and column_name = 'cnamekna' 
    and not exists
    (select 1
    FROM
        pg_views
    where
        schemaname='public'
    and viewname=table_name)
    union
    select t.relname AS TABNM,t.attname AS COLNM
from
    ( 
        select
            A.attname
            , C.relname
            , col_description(A.attrelid, A.attnum) AS COMMENT
            , format_type(A.atttypid, A.atttypmod) AS TYPE
            , A.attname AS NAME
            , A.attnotnull AS NOTNULL 
        FROM
            pg_class AS C
            , pg_attribute AS A 
        WHERE
            1 = 1 
            AND A.attrelid = C.oid 
            AND A.attnum > 0
    ) t 
where
    t.comment like '%カナ氏名%' 
    and t.attname <> 'cnamekna' 
    and t.type='character varying(40)'
    and not exists ( 
        select
            1 
        FROM
            pg_views 
        where
            schemaname = 'public' 
            and viewname = t.relname
    )
; 
/*========================================================================
  サブ・ルーチン
========================================================================*/
--
/*========================================================================
  メイン・ルーチン
========================================================================*/
BEGIN                                           --
-- // 開始ログの出力
  --CALL P_INS_PSLOG('INFO', '○開始○', 'cnamekna'); 
--
FOR rXCSQL IN csrXCSQL LOOP

        --SQL実行
         WsSQL := ' UPDATE ' 
        || rXCSQL.TABNM  
        || ' SET ' || rXCSQL.COLNM 
        || ' = '|| '''ﾓﾍｼﾞ''' 
        || ' WHERE ' || rXCSQL.COLNM || ' IS NOT NULL';
      EXECUTE WsSQL;
--
END LOOP; 
--
-- // 終了ログの出力
--CALL P_INS_PSLOG('INFO', '○終了○', 'cnamekna'); 
/*========================================================================
  メイン・ルーチン／例外処理
 ========================================================================*/
EXCEPTION                                       --
WHEN OTHERS THEN 
--CALL P_INS_PSLOG( 'ERROR', SQLSTATE || ':' || SQLERRM, 'cnamekna_SQL:'||WsSQL); 
--
RAISE; 
/*========================================================================
  メイン・ルーチン／終了
 ========================================================================*/
END; 

$procedure$
;

-- Permissions

/*========================================================================
 ➁　Sample
 =========================================================================

-- PROCEDURE: public.cshainno()

-- DROP PROCEDURE public.cshainno();

CREATE OR REPLACE PROCEDURE public.cshainno(
    )
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
/*------------------------------------------------------------------------------
  定数定義
  */
--
/*------------------------------------------------------------------------------
  変数定義
  */
--
QREC RECORD;
WsSQL1 varchar(30000);
WsSQL varchar(30000);
WsSQLT varchar;
WsSQLC varchar;

--
--
/*------------------------------------------------------------------------------
  カーソル定義
  */
--
csrXCSQL CURSOR FOR
select
    table_name AS TABNM
    , column_name AS COLNM
from
    information_schema.columns
where
    table_name <> 'pg_stat_statements'
    and table_schema = 'public'
    and column_name = 'cshainno'
    and not exists
    (select 1
    FROM
        pg_views
    where
        schemaname='public'
    and viewname=table_name)
    union
    select t.relname AS TABNM,t.attname AS COLNM
from
    (
        select
            A.attname
            , C.relname
            , col_description(A.attrelid, A.attnum) AS COMMENT
            , format_type(A.atttypid, A.atttypmod) AS TYPE
            , A.attname AS NAME
            , A.attnotnull AS NOTNULL
        FROM
            pg_class AS C
            , pg_attribute AS A
        WHERE
            1 = 1
            AND A.attrelid = C.oid
            AND A.attnum > 0
    ) t
where
    t.comment like '%職員番号%'
    and t.attname <> 'cshainno'
    and t.type='character varying(10)'
    and not exists (
        select
            1
        FROM
            pg_views
        where
            schemaname = 'public'
            and viewname = t.relname
    )
;

/*========================================================================
  サブ・ルーチン
========================================================================*/
--
/*========================================================================
  メイン・ルーチン  ========================================================================*/
BEGIN                                           --
-- // 開始ログの出力
  --CALL P_INS_PSLOG('INFO', '○開始○', 'CSHAINNO');
--
FOR rXCSQL IN csrXCSQL LOOP

       --SQL実行
         WsSQL := ' UPDATE '
        || rXCSQL.TABNM  
        || ' SET ' || rXCSQL.COLNM
        || ' = case when ' || rXCSQL.COLNM || '::numeric+11111 >9999999999 then ' || '''X''||' ||'substr(' || rXCSQL.COLNM  || '::numeric+11111 ,3,9)'
        || ' else ('|| rXCSQL.COLNM || '::numeric+11111)::varchar end'
        || ' where lf_to_number(' || rXCSQL.COLNM  || '::varchar) is not null';        
      EXECUTE WsSQL;
--
END LOOP;
--
-- // 終了ログの出力
--CALL P_INS_PSLOG('INFO', '○終了○', 'CSHAINNO');

/*========================================================================
  メイン・ルーチン／例外処理
 =======================================================================*/
EXCEPTION                                       --
WHEN OTHERS THEN
--CALL P_INS_PSLOG( 'ERROR', SQLSTATE || ':' || SQLERRM, 'CSHAINNO_SQL:'||WsSQL);
--
RAISE;
/*========================================================================
  メイン・ルーチン／終了
=======================================================================*/
END;
$BODY$;

GRANT EXECUTE ON PROCEDURE public.cshainno() TO postgres;

GRANT EXECUTE ON PROCEDURE public.cshainno() TO PUBLIC;