Function to update records from JSON in Postgres

Question

I want to create a function which will accept the input parameter as below:

[
    {
        "id": 1,
        "organization_name": "Test Solutions",
        "website": "[www..test.solutions]",
        "contact_person": "John Doe"
    }
]

I can update the records from the simple query in pgadminIII it didn't give me any error but there no other way to use this query other than function in JPA which will take input in a query. Below is my update query:

UPDATE organization p
    SET (organization_name,emails,contacts,relation_type_id,website,cst_no,vat_tin_no,contact_person)= 
    (COALESCE(ab.organization_name, p.organization_name)
    ,COALESCE(ab.website, p.website)
    ,COALESCE(ab.contact_person, p.contact_person)
         )
    FROM (
      select * from  json_populate_recordset 
    (null::organization,'[{"id":1,"organization_name":"Test Solutions","website":"[www..test.solutions]","contact_person":"John Doe"}]')
      ) ab
    WHERE p.id = ab.id;

How can I create a function in JPA which will work by passing JSON as shown above:

I tried one function which gives me error which I pass parameter. Below is the function syntax:

CREATE OR REPLACE FUNCTION cloudschema.org_bulk_edit_json(IN j json)RETURNS  SETOF record AS
$BODY$
BEGIN

  UPDATE cloudschema.organization p
    SET (organization_name,emails,contacts,relation_type_id,website,cst_no,vat_tin_no,contact_person)= 
    (COALESCE(ab.organization_name, p.organization_name)
    ,COALESCE(ab.website, p.website)
    ,COALESCE(ab.contact_person, p.contact_person)
         )
    FROM (
      select * from  json_populate_recordset 
    (null::cloudschema.organization,$1)
      ) ab
    WHERE p.id = ab.id;
  END;
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

What is the error you get and how exactly are you calling your function? — user330315
– user330315, Commented Aug 21, 2017 at 11:32

klin · Accepted Answer · 2017-08-21 11:56:54Z

Your query (and the function) cannot work because the number of columns does not match number of values. Also, the function returns nothing so it should be declared as RETURNS void.

CREATE OR REPLACE FUNCTION cloudschema.org_bulk_edit_json(IN j json)
RETURNS void AS
$BODY$
BEGIN
    UPDATE cloudschema.organization p
    SET (organization_name, website, contact_person) = 
    (
        COALESCE(ab.organization_name, p.organization_name),
        COALESCE(ab.website, p.website),
        COALESCE(ab.contact_person, p.contact_person)
    )
    FROM json_populate_recordset(null::cloudschema.organization,$1) ab
    WHERE p.id = ab.id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

If you want to update more columns, add appropriate values in SET, e.g.:

    SET (organization_name, emails, contacts, website, contact_person) = 
    (
        COALESCE(ab.organization_name, p.organization_name),
        COALESCE(ab.emails, p.emails),
        COALESCE(ab.contacts, p.contacts),
        COALESCE(ab.website, p.website),
        COALESCE(ab.contact_person, p.contact_person)
    )

Collectives™ on Stack Overflow

Function to update records from JSON in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related