andehhh andehhh - 1 year ago 74
SQL Question

Dynamic INSERT statement based on hstore keys

given following scenario:
I have a table which basically has - among others - the 2 relevant columns:

  • table_name (text)

  • data (hstore)

The hstore keys correspond to the columns of the table referenced in table_name.
I would like to automatically generate an INSERT statement based on those 2 columns wich inserts the data of each key into the columns of that referenced table. The keys of course can vary, as basically data of any table can be present in the hstore column.

All this happens within a bigger function. The keys/colums are available in an array.


table_name = test_table
data = "id"=>"1", "trans"=>"4", "comment"=>"asdf"

resulting Statement:

INSERT INTO test_table (id, trans, comment) VALUES (1,4,'asdf');

The goal is to have a function which dynamically inserts the data into the right table, no matter what is specified in table_name and data.

Answer Source

Something like this could work:

CREATE OR REPLACE FUNCTION test.ins(table_name character varying, data jsonb)
    RETURNS character varying AS
    _keys character varying;
    _vals character varying;
    _comma character varying;
    x RECORD;
    _keys = '';
    _vals = '';
    _comma = '';
    FOR x IN (SELECT * FROM jsonb_each(data))
        _keys = _keys || _comma || x.key;
        _vals = _vals || _comma || x.value;
        _comma = ', ';
    RETURN 'INSERT INTO ' || table_name || ' (' || _keys || ') VALUES (' || _vals || ')';
    COST 100;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download