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

Question

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 ?

klin · Accepted Answer · 2022-08-17 11:34:59Z

1

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

edited Aug 17, 2022 at 11:34

answered Aug 16, 2022 at 10:34

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jc John Over a year ago

still having this error SQL Error [22P02]: ERROR: invalid input syntax for type json Detail: Expected end of input, but found ":".

klin Over a year ago

See the updated answer.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related