Your Location is: Home > Jsonb

Convert a table record to jsonb and assign to a variable

From: Fidschi View: 2764 Mohan Gundlapalli 

Question

I am facing an error in converting a table record to jsonb and then assign it to a variable. I am getting the follwing error. I also tried with to_jsonb function and it too gave the same error.

ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead.

Following is the code I am using. Table users has a lot of columns about 100. I hope this does not matter.

do
$$
declare
json_elements jsonb;
begin
    select json_elements = row_to_json(t)::jsonb from users t limit 1;
    select json_elements;
end;
$$

Note: I am using postgres 9.5 version.

Best answer

I changed the code as follows and it worked.

do
$$
declare
json_elements jsonb;
begin
    select row_to_json(t)::jsonb into json_elements from users t limit 1;
    raise notice '%s' json_elements;
end;
$$

The error was in the select statements. I am from SQL server background and i wanted to see the value by using the select. So I changed the first select to select into and the replaced the second one with raise notice. it started working.