在Postgresql中同一张表不同行之间的全部列（不指定列）更新Update all columns (unspecified columns) between different rows

Update all columns (unspecified columns) between different rows in the same table in PostgreSQL

一、概述

需求描述: 有一张表A，需要在R2记录中列为空的情况下，更新为R1记录中的值。
Requirement description: There is a table A that needs to be updated to the value in the R1 record when the column in the R2 record is empty.

二、实现思路

1.在不确定列的情况下，首先获取表的全部列名称

In the case of uncertain columns, first obtain all column names of the table

从pg_attribute中获取表的全部列：

SELECT attname FROM pg_attribute WHERE attrelid = table_name::regclass AND attnum > 0 AND NOT attisdropped';

2.判断如果为空的情况下应用R1的值

Apply the value of R1 if it is empty

用到函数COALESCE:

SELECT COALESCE(T2.COL,T1.COL) FROM T1,T2 ...

3.遍历组合更新语句

Traverse combination update statements

使用循环遍历COALESCE:

FOR col_name IN EXECUTE col_query LOOP
        update_query := update_query || ', '|| col_name || 
           ' (SELECT COALESCE(t2.' || col_name || ', t1.' || col_name || 
           ') FROM '| table_name ||' t1 WHERE t1.id = '''|| from_id || ''')';
    END LOOP;

4.完整语句

complete sentence

CREATE OR REPLACE FUNCTION update_record_by_id
(
    from_id VARCHAR, to_id VARCHAR, table_name VARCHAR
)
RETURNS VOID AS $$
DECLARE
    col_name TEXT;
    update_query VARCHAR := '';
    col_query VARCHAR := 'SELECT attname FROM pg_attribute WHERE attrelid = ''' 
    || table_name || '''::regclass AND attnum > 0 AND NOT attisdropped';
BEGIN
    FOR col_name IN EXECUTE col_query LOOP
        update_query := update_query || ', '|| col_name || 
           ' (SELECT COALESCE(t2.' || col_name || ', t1.' || col_name || 
           ') FROM '| table_name ||' t1 WHERE t1.id = '''|| from_id || ''')';
    END LOOP;

    update_query := 'UPDATE '| table_name ||' t2 SET '|| 
            substring(update_query, 3) || ' 
                WHERE t2.id = '''|| to_id ||''' 
                AND t2.id <> '''|| from_id ||'''';

    -- RAISE NOTICE 'Update Query: %', update_query;  -- 打印测试

    EXECUTE update_query;
END;
$$ LANGUAGE plpgsql;

二、应用场景·Application scenarios

在已知表名称和唯一列名称的情况下，同一表中的数据进行空值更新。

When the table name and unique column name are known, perform null value updates on data in the same table.