主页 / dba / 问题 / 287201
Accepted
FranckT
Asked: 2021-03-18 08:28:34 +0800 CST

从特定模式获取所有表的所有列的不同值

  • 772

我想为特定模式的每个表中的所有列获取所有不同的值。

首先，我在一个 new_table (myschema.dico) 中总结了我所有的表/列

  CREATE TABLE myschema.dico AS (
    WITH tables AS (
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'myschema' and table_type ='BASE TABLE' and table_name LIKE 'XXX' --criterias if needed
        ORDER BY table_name
                    )
    SELECT tables.table_name, 
    column_name, 
    concat(tables.table_name,'.',column_name) AS address, 
    concat ('myschema.',tables.table_name) AS home
    FROM information_schema.columns
    RIGHT JOIN tables ON tables.table_name = columns.table_name
    ORDER BY 1,2
    ;

然后，添加了一个新列来更新我的不同值

ALTER TABLE myschema.dico ADD COLUMN dico TEXT;

myschema.dico 表如下所示：

表名 列名 地址 家 迪科
表格1 一 表1.one myschema.table1
表格1 二 表1.二 myschema.table1
表格1 三 表1.三 myschema.table1
表2 啊啊啊 表2.aaa myschema.table2
表2 bbb 表2.bbb myschema.table2

然后我尝试创建一个函数来使用所有不同的值更新我的所有“dico”列的值：

CREATE OR REPLACE FUNCTION dico_columns() RETURNS void AS $$
DECLARE

  cur_table CURSOR FOR 
    SELECT *
    FROM myschema.dico
    FOR UPDATE;
 cur_table_row RECORD ;
 
  cur_dico2 CURSOR FOR 
    SELECT *
    FROM myschema.dico
    FOR UPDATE;
 cur_dico2_row RECORD ;
 
BEGIN

    OPEN cur_table;
    FETCH cur_table INTO cur_table_row;
    LOOP 
        EXIT WHEN NOT FOUND;
        OPEN cur_dico2;
        FETCH cur_dico2 INTO cur_dico2_row;
        LOOP
            EXIT WHEN NOT FOUND;
            
            IF cur_table_row.home IS NOT NULL 
            THEN  UPDATE myschema.dico 
                  SET dico= ( 
                              SELECT json_build_object(colname, array_agg(DISTINCT val))
                              FROM cur_table_row.home --refers to myschema.table 
                              CROSS JOIN LATERAL json_each_text(row_to_json(cur_table_row.table_name)) AS j(colname,val) --table_name = table
                              WHERE colname=cur_table_row.column_name --refers to table.column
                              GROUP BY colname)
                   WHERE CURRENT OF cur_dico2;

            END IF;
            FETCH cur_dico2 INTO cur_dico2_row;
        END LOOP;
        CLOSE cur_dico2;
        FETCH cur_table INTO cur_table_row;
    END LOOP;
    CLOSE cur_table;
    
END ;
$$ LANGUAGE plpgsql;
    
SELECT dico_columns();

不幸的是，似乎无法使用“FROM cursor_row.value”。我尝试了很多东西，但我不熟悉功能。

你有更好的主意吗？

目前在 postgres 9.5 上。

postgresql json

2 个回答

  1. Best Answer

    它需要一些改进，但要向您展示如何开始

    但基本上使用 public.Selecttext 使更新成为一个简单的更新，并且您可以实现相同的操作，而无需循环

    CREATE TABLE dico
    ("table_name" varchar(6), "column_name" varchar(5), "address" varchar(12), "home" varchar(15), "dico" TEXT)
;
    
INSERT INTO dico
    ("table_name", "column_name", "address", "home", "dico")
VALUES
    ('table1', 'one', 'table1.one', 'myschema.table1', ''),
    ('table1', 'two', 'table1.two', 'myschema.table1', ''),
    ('table1', 'three', 'table1.three', 'myschema.table1', ''),
    ('table2', 'aaa', 'table2.aaa', 'myschema.table2', ''),
    ('table2', 'bbb', 'table2.bbb', 'myschema.table2', '')
;

    CREATE TABLE table1
    ("one" varchar(6), "two" varchar(5), "three" varchar(12))
;
    
INSERT INTO table1
    ("one", "two", "three")
VALUES
    ('a', 'd', 'f'),
    ('b', 'd', 'h'),
    ('a', 'd', 'g'),
    ('c', 'e', 'f'),
    ('a', 'e', 'g')
;

CREATE TABLE table2
    ("aaa" varchar(6), "bbb" varchar(5))
;
    
INSERT INTO table2
    ("aaa", "bbb")
VALUES
    ('aa', 'dd'),
    ('bbb', 'd'),
    ('aaa', 'd'),
    ('ab', 'ee'),
    ('aba', 'ee')
;

    CREATE OR REPLACE FUNCTION public.Selecttext(tablen text, coln text)
RETURNS SETOF RECORD
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN 
    RETURN QUERY EXECUTE 'SELECT textcol::TEXT FROM (SELECT json_build_object(colname, array_agg(DISTINCT val)) AS textcol FROM '
    ||$1||E'  CROSS JOIN LATERAL json_each_text(row_to_json( '
    ||$1||E' )) AS j(colname,val) WHERE colname =  \''
    ||$2||E'\' GROUP BY colname) t2';
END 
$BODY$;

    CREATE OR REPLACE FUNCTION dico_columns() RETURNS void AS $$
DECLARE

  cur_table CURSOR FOR 
    SELECT "table_name", "column_name", "address", "home", "dico"
    FROM dico
    ;
   _tabl text;
   _coln text;
   _address text;
   _home text;
   _dico text;
 

BEGIN

    OPEN cur_table;
    LOOP
    FETCH cur_table INTO _tabl,_coln,_address,_home,_dico;
     
        EXIT WHEN NOT FOUND;

  IF _home IS NOT NULL  THEN
               UPDATE dico 
                SET dico= (sELECT textcol FROM  Selecttext(_tabl,_coln) AS t(textcol TEXT)) 
                WHERE "table_name" = _tabl AND "column_name"  = _coln
                AND "home"  = _home;
                   
  END IF;

    END LOOP;
    CLOSE cur_table;
    
END ;

$$ LANGUAGE plpgsql;
    
;

    SELECT dico_columns()

    | dico_columns |
| :----------- |
| |

    SELECT json_build_object(colname, array_agg(DISTINCT val)) AS textcol
FROM table1
CROSS JOIN LATERAL json_each_text(row_to_json(table1)) AS j(colname,val) 
WHERE colname='one' 
GROUP BY colname

    | 文本库 |
| :------------------------ |
| {"一个" : ["a","b","c"]} |

    sELECT textcol FROM  Selecttext('table1','one') AS t(textcol TEXT)

    | 文本库 |
| :------------------------ |
| {"一个" : ["a","b","c"]} |

    sELECT textcol FROM  Selecttext('table1','two') AS t(textcol TEXT)

    | 文本库 |
| :----------------- |
| {"二" : ["d","e"]} |

    SELECT * frOM dico

    表名 | 列名 | 地址 | 主页 | 迪科                                   
:--------- | :------------ | :----------- | :---------------- | :----------------------------
表1 | 一个 | table1.one | myschema.table1 | {“一个”：[“a”，“b”，“c”]}                
表1 | 二 | 表1.2 | myschema.table1 | {“二”：[“d”，“e”]}                    
表1 | 三 | 表1.三 | myschema.table1 | {“三”：[“f”，“g”，“h”]}              
表2 | 啊！表2.aaa | myschema.table2 | {"aaa" : ["aa","aaa","ab","aba","bbb"]}
表2 | bbb | 表2.bbb | myschema.table2 | {"bbb" : ["d","dd","ee"]}

    db<>在这里摆弄

    • 1

  2. 很棒的工作 ！

      CREATE OR REPLACE FUNCTION public.Selecttext(tablen text, coln text, homen text)
    RETURNS SETOF RECORD
    LANGUAGE 'plpgsql'
    AS $BODY$
    BEGIN 
        RETURN QUERY EXECUTE 'SELECT textcol::TEXT FROM (SELECT json_build_object(colname, array_agg(DISTINCT val)) AS textcol FROM '
        ||$3||E'  CROSS JOIN LATERAL json_each_text(row_to_json( '
        ||$1||E' )) AS j(colname,val) WHERE colname =  \''
        ||$2||E'\' GROUP BY colname) t2';
    END 
    $BODY$;

    我添加了参数 home，因为它在模式 <>public 中不起作用，现在它在我身边起作用。

    CREATE OR REPLACE FUNCTION dico_columns() RETURNS void AS $$
DECLARE

  cur_table CURSOR FOR 
    SELECT "table_name", "column_name", "address", "home", "dico"
    FROM myschema.dico
    ;
   _tabl text;
   _coln text;
   _address text;
   _home text;
   _dico text;
 

BEGIN

    OPEN cur_table;
    LOOP
    FETCH cur_table INTO _tabl,_coln,_address,_home,_dico;
     
        EXIT WHEN NOT FOUND;

  IF _home IS NOT NULL  THEN
               UPDATE myschema.dico 
                SET dico= (sELECT textcol FROM  Selecttext(_tabl,_coln, _home) AS t(textcol TEXT)) 
                WHERE "table_name" = _tabl AND "column_name"  = _coln
                AND "home"  = _home;
                   
  END IF;

    END LOOP;
    CLOSE cur_table;
    
END ;
    • 0

相关问题