Can any one tell me how I can create an

rpc

which takes a json doc as an argument and adds not the doc itself but the data from it into a table. For instance, the

keys

will be the column names and the

values

will be the data that need to be added.

looks pretty close to working. Can you try to use

json_populate_recordset

instead of

json_populate_record

Sorry for the late reply. A couple questions for this solution. What is the

r

in

SELECT r.* FROM json_populate_record(NULL::db_transactions, _js) r;

? Like what does it do? Here

select insert_json(json_build_object('t_date', now(),'category','random_category','amount',120,'deb_acc','acc_1','cred_acc','acc_2'));

you are giving comma separated values to this rpc which should build a json. Is there no way to givea json directly? Kinda like this:

select insert_json({'t_date':now(),'category':'random_category','amount':120,'deb_acc':'acc_1','cred_acc':'acc_2'});

. For context, the reason I want to do this is this. My client-side code has a

DataModel

and it exclusively works using that model. When it is the time to send data to the backend, this class has a method called

toMap()

. This converts it into a json. Because of this

DataModel

, the json document's keys are ALWAYS correct column names and values ALWAYS follow the constraints. In normal circumstances, I do this:

dart
addData(DataModel data) {
    var response = _databaseService
        .insert(
          data.toMap(),
        )
        .execute();
    return response;
  }

Instead of this, I want to do this:

dart
addData(DataModel data) {
    var response = _databaseService
        .rpc(
           insert_data,
           params : {
             data.toMap(),
           }
        )
        .execute();
    return response;
  }

The reason to do this is this will allow me to add rows to more than one table in one go of just one insertion call. Hopefully that makes sense.

1.

json_build_object

is just building json in sql that I used to test the function. It makes sense that you don’t need this as your toMap() is doing that.

So what you mean is, I could just call the

rpc

in its current state from my code by giving it a

param

like this

datamodel.toMap()

and it would work.

json_build_object

is only required within sql, right? I just find it a little bit weird. Like why would it accept a json doc from my client-side code but wont accept it within the sql editor.

As that’s not how you do json in sql. It usually needs single quotes etc. I’ll see if I find and example

thats probably it. Let me know if you find one.

well this works

sql
select insert_json('
  {
    "t_date": "2021-02-13T12:05:33+11:00","category":"random_category","amount": 120,"deb_acc": "acc_1","cred_acc": "acc_2"
  }
');

Though I don't know how to sub the hard coded date and use now() this this syntax. thats why I just use

json_build_object

I want to handle all of the server side data insertion in one BaaS call.

insert

would just add that data to the one table I specify. By using

rpc

, I can use the data in the form of json and do some conditional checks on certain field values and insert the appropriate values in the appropriate tables. Does that make sense?

default is what it is when its left off the list. though if you have the key

{id: null}

you are setting it to null If you want default to work leave it out of the json object completely.

It is not in the json. I am using the exact piece of code you wrote (with the hard coded date). There is no

id

field in it. Shouldnt it use the default value then.

lets be explicit. can you share your code in a minimum reproducible example? Or just update my example to be able to reproduce the problem your experiencing?

sql
create or replace function insert_json(_js json)
returns void
language plpgsql
as $$

begin
  INSERT INTO db_transactions
  SELECT r.* FROM json_populate_record(NULL::db_transactions, _js) r;
  
end;
$$;

select insert_json('
  {
    "t_date": "2021-02-13T12:05:33","category":"random_category","amount": 120,"deb_acc": "acc_1","cred_acc": "acc_2"
  }
');

There is nothing to hide here. I am just testing out this functionality at the moment. My table was manually created. It has all these columns and some more:

id

which is not nullable and defaults to auto genrated uuid. Other than that, there are two more columns but they are nullable.

I get it. the syntax of insert statements need a list of values when your leaving them out

sql
INSERT INTO db_transactions(t_date, category,etc)

oh. I will try that but doesnt that defeat the whole purpose of using json where they keys are the column names. At this point, why not just have all of the values directly inside the

params

instead of just one json doc. I mean the

insert

method in my client-side code took a json doc and automatically did what I am trying to brute force right now.

I think there is a better way to solve this problem and that postgrest has a neater way of solving this.

So what you mean is that I have one table as a throughway in which I

insert

data from client side using the

insert

call and a json doc (like I was doing before). With this insertion, a trigger would call a function which would take the new row and then do what I am trying to do ultimately.

no exactly for the example out of the docs

js
const { data, error } = await supabase
  .from('cities')
  .insert([
    { name: 'The Shire', country_id: 554 }
  ])

if you needed custom logic you can have a trigger on a table or a view that does it.

That could work... I could set up the trigger to fire up the function before insert so that I could make the changes as per the conditions I need. That might work.