PostgreSQL JSON Object – Selecting json_object() into List in PostgreSQL Function

plpgsqlpostgresql

Here i am trying to get all the select values in a varible.

 DECLARE
   ite json;
 BEGIN   
   select INTO ite from json_object('{1}','{1}');    
 RETURN ite;

when i call this function i got the result null.

Best Answer

Have a look at SELECT INTO in Postgres docs.

SELECT INTO: define a new table from the results of a query

Synopsis

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

Examples

Create a new table films_recent consisting of only recent entries from the table films:

SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';

Try the next syntax:

CREATE OR REPLACE FUNCTION fn_get_json()
RETURNS text AS
$$
DECLARE
   ite text;
BEGIN   
   select * into ite from json_object('{1}','{1}');
   return ite;
END
$$
LANGUAGE plpgsql;

select * from fn_get_json();

| fn_get_json |
| :---------- |
| {"1" : "1"} |

db<>fiddle here

Or as pointed out in comments a reduced version:

CREATE OR REPLACE FUNCTION fn_get_json()
RETURNS json AS
$$
BEGIN   
   return json_object('{1}','{1}');
END
$$
LANGUAGE plpgsql;

Or even simpler with a SQL function:

CREATE OR REPLACE FUNCTION fn_get_json()
RETURNS json AS
$$
   select json_object('{1}','{1}');
$$
LANGUAGE sql;
Related Question