postgresql - jsonb_populate_record/jsonb_populate_recordset 应该返回一个表

Question

目前我尝试基于 postgresql jsonb 制作历史表，目前作为示例我有两个表:

CREATE TABLE data (id BIGSERIAL PRIMARY KEY, price NUMERIC(10,4) NOT NULL, article TEXT NOT NULL, quantity BIGINT NOT NULL, lose BIGINT NOT NULL, username TEXT NOT NULL);
CREATE TABLE data_history (id BIGSERIAL PRIMARY KEY, data JSONB NOT NULL, username TEXT NOT NULL);

历史表行为是一个简单的历史(用户名可以避免)。我用触发器填充历史数据:

CREATE OR REPLACE FUNCTION insert_history() RETURNS TRIGGER AS $$
  BEGIN

    INSERT INTO data_history (data, username) VALUES (row_to_json(NEW.*), NEW.username);
    RETURN NEW;

  END;
$$ LANGUAGE plpgsql;

现在我尝试将历史填充回数据表:

SELECT jsonb_populate_record(NULL::data, data) FROM data_history;

但是现在结果将是一个元组而不是一个表:

        jsonb_populate_record        
-------------------------------------
 (1,45.4500,0A45477,100,1,c.schmitt)
 (2,5.4500,0A45477,100,1,c.schmitt)
(2 rows)

有什么方法可以将数据作为表数据取回吗？我知道也有 jsonb_populate_recordset，但是它不接受查询？!

Answer 1

jsonb_populate_record() 返回一个行类型(或记录类型)，因此如果您在 SELECT 子句中使用它，您将得到一个列，这是一个行类型。

为避免这种情况，请在 FROM 子句中使用它(使用隐式 LATERAL JOIN):

SELECT r.*
FROM   data_history,
       jsonb_populate_record(NULL::data, data) r

从技术上讲，下面的语句也可以工作

-- DO NOT use, just for illustration
SELECT jsonb_populate_record(NULL::data, data).*
FROM   data_history

但它会为 data 中的每一列调用 jsonb_populate_record()(由于引擎限制)。