CODEWITHSUNDEEP
jsonpostgresqljpaplpgsql
SushantParkhe
SushantParkhe

Reputation: 85

Function to update records from JSON in Postgres

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;

Upvotes: 2

Views: 426

Answers (1)

klin
klin

Reputation: 121624

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)
    )

Upvotes: 2

Related Questions