[SOLVED] Replace hstore with json by creating a column variable using NEW #= in postgres

Replace hstore with json by creating a column variable using NEW #= in postgres

I was searching for creating a column/variable in postgres and I found out a code that is using hsstore extension to create a column variable.

I found out this code

    temp_sql_string:='"'||p_k||'"=>"'||nextval(seq::regclass)||'"';
    NEW := NEW #= temp_sql_string :: public.hstore;

from : https://dba.stackexchange.com/questions/82039/assign-to-new-by-key-in-a-postgres-trigger

Now, what i want is to get rid of the hstore extension and I want to use json. I've tried using this but doesn't work.

temp_sql_string:='"'||p_k||'":"'||nextval(seq::regclass)||'"';          
NEW := NEW #= temp_sql_string::json;

any idea ?

Solution

The #= operator is specific for hstore. Use json(b)_populate_record() as a JSON(B) equivalent:

new:= json_populate_record(new, json_build_object(key, value));

temp_sql_string is not a valid JSON representation due to the lack of curly braces. In general, you should avoid interpolating variables in JSON strings by using appropriate functions instead. Assuming p_k and seq are text variables, this should work fine:

new:= json_populate_record(new, json_build_object(p_k, nextval(seq::regclass)));