0

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.

1

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

select values in a varible

I think what you want here is a jsonb_agg which aggregates values as a JSON array.

SELECT jsonb_agg(foo)
FROM ( VALUES (1,2,'foo'), (2,3,'bar') ) AS foo(a,b,c);
                          jsonb_agg                           
--------------------------------------------------------------
 [{"a": 1, "b": 2, "c": "foo"}, {"a": 2, "b": 3, "c": "bar"}]

There is also jsonb_object_agg(name, value)

SELECT jsonb_object_agg(a,b)
FROM ( VALUES ('key1','value1'),('key2','value2') ) AS t(a,b);
           jsonb_object_agg           
--------------------------------------
 {"key1": "value1", "key2": "value2"}

Though I admit it's somewhat hard to tell you've provided so little.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.