pl/pgsql使用父级子表和ROWTYPE数组插入CTE

问题描述：

我有两个历史表。一个是父母，第二个是细节。在这种情况下，它们是追踪另一个表中变化的历史表。pl/pgsql使用父级子表和ROWTYPE数组插入CTE

CREATE TABLE IF NOT EXISTS history (
    id serial PRIMARY KEY, 
    tablename text, 
    row_id integer, 
    ts timestamp, 
    username text, 
    source text, 
    action varchar(10) 
); 

CREATE TABLE IF NOT EXISTS history_detail (
    id serial PRIMARY KEY, 
    master_id integer NOT NULL references history(id), 
    colname text, 
    oldval text, 
    newval text 
);

然后我有函数将现有的行与新行进行比较。比较看起来像是一个直截了当的我。我正在努力的部分是当我想将差异插入到我的历史表中时。在比较过程中，我将差异存储到history_detail的数组中，当然那时我不知道id或父表行会是什么。那是我挂断电话的地方。

CREATE OR REPLACE FUNCTION update_prescriber(_npi integer, colnames text[]) RETURNS VOID AS $$ 
DECLARE 
    t text[]; 
    p text[]; 
    pos integer := 0; 
    ts text; 
    updstmt text := ''; 
    sstmt text := ''; 
    colname text; 
    _id integer; 
    _tstr text := ''; 
    _dtl history_detail%ROWTYPE; 
    _dtls history_detail[] DEFAULT '{}'; 
BEGIN 
    -- get the master table row id. 
    SELECT id INTO _id FROM master WHERE npi = _npi; 

    -- these select all the rows' column values cast as text. 
    SELECT unnest_table('tempmaster', 'WHERE npi = ''' || _npi || '''') INTO t; 
    SELECT unnest_table('master', 'WHERE npi = ''' || _npi || '''') INTO p; 

    -- go through the arrays and compare values 
    FOREACH ts IN ARRAY t 
    LOOP 
      pos := pos + 1; 
      -- pos + 1 becuse the master table has the ID column 
      IF p[pos + 1] != ts THEN 
        colname := colnames[pos]; 
        updstmt := updstmt || ', ' || colname || '=t.' || colname; 
        sstmt := sstmt || ',' || colname; 
        _dtl.colname := colname; 
        _dtl.oldval := p[pos + 1]; 
        _dtl.newval := ts; 
        _dtls := array_append(dtls, dtl); 
        RAISE NOTICE 'THERE IS a difference at for COLUMN %, old: %, new: %', colname, p[pos + 1], ts; 
      END IF; 

    END LOOP; 

    RAISE NOTICE 'dtls length: %', array_length(dtls,1); 
    RAISE NOTICE 'dtls: %', dtls; 
    RAISE NOTICE 'done comparing: %', updstmt; 
    IF length(updstmt) > 0 THEN 
      WITH hist AS (
        INSERT INTO history 
        (tablename, row_id, ts, username, source, action) 
        VALUES 
        ('master', _id, current_timestamp, 'me', 'source', 'update') 
        RETURNING * 
      ), dtls AS (
        SELECT hist.id_ 
      INSERT INTO history_detail 
-- 
-- this is where I am having trouble 
-- 
      ; 

      _tstr := 'UPDATE master 
        SET ' || substr(updstmt,2) || ' 
        FROM (SELECT ' || substr(sstmt,2) || ' FROM tempmaster WHERE npi = ''' || _npi || ''') AS t 
        WHERE master.id = ' || _id || ';'; 
      EXECUTE _tstr; 
    END IF; 
END; 
$$ LANGUAGE plpgsql;

在理想的世界中，我将能够在声明中完成所有这些工作。我知道我可以用另一个BEGIN..END包裹的多条语句来做到这一点。我想确保我以最有效的方式做到这一点。我不认为有办法摆脱动态的EXECUTE，但希望有人比我更聪明，可以推动我朝着正确的方向前进。

感谢您的任何帮助。

答

我能够创建一个可以一次插入2个历史表的语句。

WITH hist AS (
    INSERT INTO history 
    (tablename, row_id, ts, username, source, action) 
    VALUES 
    ('master', _id, current_timestamp, 'me', 'source', 'update') 
    RETURNING id 
), dtls AS (
    SELECT (my_row).* 
    FROM unnest(_dtls) my_row 
), inserts AS (
    SELECT hist.id AS master_id, 
      dtls.colname AS colname, 
      dtls.newval AS newval, 
      dtls.oldval AS oldval 
    FROM dtls,hist 
) 
INSERT INTO history_detail 
(master_id, colname, newval, oldval) 
SELECT * FROM inserts 
;

我还是想给列更新添加的东西是不是EXECUTE声明，但我真的不认为这是可能的。

pl/pgsql使用父级子表和ROWTYPE数组插入CTE

相关推荐