CODEWITHSUNDEEP
postgresql
Christian Schmitt
Christian Schmitt

Reputation: 892

jsonb_populate_record / jsonb_populate_recordset should return a table

currently I try to make a history table based on postgresql jsonb, currently as a example I have two table's:

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);

The history table act's a simple history (the username there could be avoided). I populate the data of the history with a trigger:

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;

Now I try to populate the history back to the data table:

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

However the result will now be a tuple and not a table:

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

Is there any way to get the data back as the table data back? I know there is jsonb_populate_recordset, too, however it doesn't accept a query?!

Upvotes: 3

Views: 8713

Answers (1)

pozs
pozs

Reputation: 36244

jsonb_populate_record() returns a row-type (or record-type), so if you use it in the SELECT cluase, you'll get a single column, which is a row-type.

To avoid this, use it in the FROM clause instead (with an implicit LATERAL JOIN):

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

Technically, the statement below could work too

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

but it will call jsonb_populate_record() for each column in data (as a result of an engine limitation).

Upvotes: 7

Related Questions