Postgres Pro
Downloads
Home   >   mailing lists

Postgresql - Json syntax in INSERT RETURNING clause with INTO - Mailing list pgsql-sql

From Ron Clarke
Subject Postgresql - Json syntax in INSERT RETURNING clause with INTO
Date
Msg-id CAGVf-sOFL2UsRJsHXNAeB7S5R5tsakCTAtEpoCOAf8vZsPEMng@mail.gmail.com
Whole thread Raw
Responses Re: Postgresql - Json syntax in INSERT RETURNING clause with INTO  (Vijaykumar Jain <vijaykumarjain.github@gmail.com>)
Re: Postgresql - Json syntax in INSERT RETURNING clause with INTO  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-sql
Tree view
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

           

pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: Select for update
Next
From: Vijaykumar Jain
Date:
Subject: Re: Postgresql - Json syntax in INSERT RETURNING clause with INTO