My quick searching doesn’t appear to have revealed the answer and forgive me if this is common knowledge. We are trying to modularize a prepared sql statement and we have run into an error. Is there any way to pass the table name and column names as variables into a prepared statement on db.sqlexecute? We tried setting our ps = “INSERT INTO $1…” where $1 is the name of the table but keep getting errors. Is this possible or are we barking up the wrong tree?
Thanks
Yes, wrong tree. The ps will take data that might come from the user, i.e., data out of your control. You’d have to build the ps with the right table and column names.
However, you do have an option. Create a SQL function that you can call with your parameters including table name, then craft the ps around that. The function will build and call insert. But I’m not sure there is any real advantage over just building the ps fresh each time.
@Kem Tekinay I understand the problem with allowing the user to potentially select the table, but what we are trying to do is allow for our old system from which we are migrating to talk to PostgreSQL db via Aloe. We’re trying to allow the old system to identify the table in which the data is being updated ( or inserted but not deleted ) and to pass columns as a parameter so that we don’t have to hard code each and every column from each and every table. My thought was if we passed the columns as a string that was formatted as you would within a prepared statement it could be inserted quite easily along with values and the like. The problem we’re running into is that if we cannot pass these as parameters and write to a ps with variables it seems we will have to loop through n times to rewrite the sql statement each time. Is that the way to go? Not using a prepared statement and just looping through the SQL constructing it on the fly kind of like how you did with your encrypted example at XDC?
Without seeing your code, yes, that sounds like the way to go.