PostgreSQL function parameters JSON to TYPE

Question

I try to connect my NodeJS API to my database.

I have created some type of object for the transport. I use LANGUAGE plpgsql.

One example :

API_CAR: this type exists on my database

I receive an object API_CAR in JSON

{
"car_id": 18,
"car_brand": "AUDI",
"car_model": "A4",
"car_nb_places": 5,
"car_picture_url": "http://...",
"horse_power": 3
}

And I would like to pass the data directly to my function :

SELECT * FROM carPATCH( ... );

I know that I can CAST it like this:

SELECT * FROM carPATCH
(
   (18,'et','vel',5,'Donec dignsque',3):: API_CAR
);

But it's complicated to do this trick.

My question is what you would advise:

Is it better to do this transformation outside the database in NodeJS or can I pass this JSON directly inside and transform it after?

I think it should be possible to transform a JSON to another type but I don't know how to do it.

This is a simple example, but I have some big structure to forward to the database.

Answer 1

One can use json_populate_record() to achieve this. It's very easy to use:

json_populate_record(null::API_CAR, <your JSON here>)

---

Exemple with NodeJS :

exports.carPATCH = function (apiKey, args, res, next) {

    let car = "'" + JSON.stringify(args.v_car.value) + "'";
    apiKey = "'" + apiKey + "'";

    DB.query.any("SELECT * FROM v01_api_patch_carPATCH" +
        "(json_populate_record(NULL::API_CAR," + car + ")," + apiKey + ")", [true])
        .then(data => {
            res = resp.response_201(res, data);
        })
        .catch(error => {
            res = resp.error(res, error);
        })
};

And after, my function work perfectly because it's an object API_CAR