Thread: Postgresql - Json syntax in INSERT RETURNING clause with INTO

Hi,

I'm trying to push some values from a json document onto a database table, and in the function return the values that have been added in the return json document.

I'm having problems 'aggregating' the return value.  Probably because I've taken the wrong approach.

Here is a simplified expression of what I've been looking at:

Thanks for taking a look, and please let me know where I'm going wrong:-

-- create test schema

CREATE SCHEMA IF NOT EXISTS jt;
-- create test table

CREATE TABLE jt.testx (
xid bigserial,
mykey varchar(20),
myvalue varchar(20),
CONSTRAINT testx_primary_key PRIMARY KEY( mykey )
);

-- drop test function

DROP FUNCTION jt.testx_put (jsonb);

-- create test function

CREATE FUNCTION jt.testx_put ( jdata jsonb )
RETURNS jsonb
AS  $function$
DECLARE
put_result jsonb[];
BEGIN

WITH jx AS ( SELECT * FROM jsonb_to_recordset(jdata ->'items')
as jc(xid bigint, mykey text, myvalue text))

INSERT INTO jt.testx (
                  mykey
                , myvalue
  )
SELECT
                   COALESCE(NULLIF(jx.mykey,''),'NEW' )::text AS mykey
                ,  COALESCE(NULLIF(jx.myvalue,''),'ZZZ' )::text AS myvalue
    FROM jx
        ON CONFLICT ON CONSTRAINT testx_primary_key
DO UPDATE
SET
                   mykey = EXCLUDED.mykey
                ,  myvalue = EXCLUDED.myvalue  
RETURNING
-- so how do I get a single Json Object back if I insert / update multiple records ??
-- This gives me one row for each item I give it...  
jsonb_build_object(
'xid', xid,
'mykey', mykey,
'myvalue', myvalue
)
INTO put_result;
RETURN put_result;

 END;
$function$
language plpgsql;


-- If we call the function with some test data:
select * from jt.testx_put('{
"action" : "insert",
    "items" : [
       {
           "xid" : "0",
            "mykey" : "G",
            "myvalue" : "Golf Stuff"
        },
        {
            "xid" : "0",
            "mykey" : "F",
            "myvalue" : "Football"
             }
    ]
}'::jsonb )
-- This results in an error
-- ERROR:  query returned more than one row
-- i.e. I'm trying to return json? 
--  
--  IF I invoke the same query with the same json...this returns two json rows...
--  
WITH jx AS ( SELECT * FROM jsonb_to_recordset('{
"action" : "insert",
    "items" : [
       {
           "xid" : "0",
            "mykey" : "G",
            "myvalue" : "Golf Stuff"
        },
        {
            "xid" : "0",
            "mykey" : "F",
            "myvalue" : "Football"
             }
    ]
}'::jsonb ->'items') AS jc(xid bigint, mykey text, myvalue text))
INSERT INTO jt.testx (
                  mykey
                , myvalue
  )
SELECT
                   COALESCE(NULLIF(jx.mykey,''),'NEW' )::text AS mykey
                ,  COALESCE(NULLIF(jx.myvalue,''),'ZZZ' )::text AS myvalue
    FROM jx
        ON CONFLICT ON CONSTRAINT testx_primary_key
DO UPDATE
SET
                   mykey = EXCLUDED.mykey
                ,  myvalue = EXCLUDED.myvalue  
RETURNING
-- how do I get a single Json Object back ???
-- This gives me one row for each item I give it...  
jsonb_build_object(
'xid', xid,
'mykey', mykey,
'myvalue', myvalue
)

-- So how do I effectively merge the output of a returning clause into a single json value
-- is this possible ??  

Thanks and regards
Ron