Is it possible to dynamically assign the Table in the GUI format of a Retool DB query?

Queries and Resources
1

I am trying to dynamically update a number of different tables in the Retool DB with JS objects. The issue I'm running into is that:

  • in GUI format, I can use the Object method in Changeset to update the tables, but I cannot pass in the table name dynamically.

  • in SQL format, I can access the different table dynamically by passing in the table name as a parameter, but I haven't found a way to update the table through passing in a JS object.

Does anyone have recommendations?

2

My two cents would be to setup the update queries, either GUI or SQL, and then trigger them using additional scope in a JS Query.

3

:rofl: I'ma start tagging you @jg80 in all my SQL questions after you pointed out I could use {{ }} in a select statement... anywho, you had me wondering if @PomroyCole could do something similar and I ended up finding this SO post which I THINK is exactly what's needed here. if that isn't, the post directly under it gives a neat plpgsql function named conditional_select. I've never actually used EXECUTE before so I don't know if it works or not, but going by the name I'd be disappointed if it didn't

4

@bobthebear - what have I gotten myself into? :wink:

The conditional joins approach you sent is a good one - but I don't think that's exactly the issue (but @PomroyCole let me know if I'm wrong).

I think the issue is that @PomroyCole has a bunch of (independent?) JS objects which are all to be used to update a bunch of (independent?) DB tables. I'm guessing that you are looking for a meta insert like

INSERT VALUES
  {{ foo }}  -- passed in JSobject.values
INTO
  {{ bar }}  -- dynamically selected table based on JS Object

So that you can run

let bat = [
 {dbValues: [1,2,3],
  dbTable: coolTable1},
 {dbValues: ['a','b','c'],
  dbTable: freshTable2}
]
for (const bat in buz):
  metaInsert.trigger( {
    Additional Scope: {
      foo: bat.dbValues,
      bar: bat.dbTable
    }
})

Which would (effectively) result in

INSERT VALUES
 (1,2,3)
INTO
 coolTable1;

INSERT VALUES
 ('a','b','c')
INTO
  freshTable2;

p.s. forgive the sloppy JS - there are transformations, etc that need to happen, but I think the point is at least clear...