CODEWITHSUNDEEP
sqlpostgresqlplpgsqlhstore
andehhh
andehhh

Reputation: 130

Dynamic INSERT statement based on hstore keys

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

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 which 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/columns are available in an array.

Example:

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.

Upvotes: 1

Views: 384

Answers (1)

Boris Schegolev
Boris Schegolev

Reputation: 3701

Something like this could work:

CREATE OR REPLACE FUNCTION test.ins(table_name character varying, data jsonb)
    RETURNS character varying AS
$BODY$
DECLARE
    _keys character varying;
    _vals character varying;
    _comma character varying;
    x RECORD;
BEGIN
    _keys = '';
    _vals = '';
    _comma = '';
    FOR x IN (SELECT * FROM jsonb_each(data))
    LOOP
        _keys = _keys || _comma || x.key;
        _vals = _vals || _comma || x.value;
        _comma = ', ';
    END LOOP;
    RETURN 'INSERT INTO ' || table_name || ' (' || _keys || ') VALUES (' || _vals || ')';
END
$BODY$
    LANGUAGE plpgsql IMMUTABLE
    COST 100;

Upvotes: 1

Related Questions