主页 / user-225887

FranckT's questions

Martin Hope
FranckT
Asked: 2021-04-02 00:48:45 +0800 CST
  • 0

我的表结构和一些示例数据（测试表），以及我想要的输出（结果）都可以在这里找到。

当我做

SELECT distinct colony, min(date_check), max(date_check)
from test
where protection ='Y'
group by colony

我只按菌落检测 1 个事件。

我应该使用函数还是有其他方法？

ps：如果你想到一个更好的标题来帮助用户，不要犹豫，我在挣扎。

postgresql-9.5 gaps-and-islands
Martin Hope
FranckT
Asked: 2021-03-18 08:28:34 +0800 CST
  • -1

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

首先，我在一个 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