0

Is there a formatter of pg-promise to format logical clauses especially in WHERE clauses, and SET clauses (in sql UPDATE)? Like connecting multiple keys and values of an object passed alongside a WHERE clause with ANDs without repeated writing a named or numbered parameter in SQL for each key and value.

As or similar to the following:

somePgPromiseLibFunction("WHERE ${this:name} = ${this:somePgpFormatter}", {a: 1, b: 2, c: 3});

generates:

"WHERE a = 1 AND b = 2 AND c = 3"

and

somePgPromiseLibFunction("UPDATE some_table_name SET ${this:name} = ${this:somePgpFormatter} WHERE some_criteria", {a: 1, b: 2, c: 3});

generates:

"UPDATE some_table_name SET a = 1, b = 2, c = 3 WHERE some_criteria"

I know one can reference this and use ${this:name}and ${this:csv} forINSERT clauses, yielding a great advantage in developers' hands by removing the necessity of repeatedly typing, and modifying the INSERT and VALUES clauses. This makes the queries immune to schema change.

Cœur
  • 37,241
  • 25
  • 195
  • 267
sçuçu
  • 2,960
  • 2
  • 33
  • 60
  • Please provide a complete example of the Data + SQL that you want generated from that data. And for the updates see [helpers.update](http://vitaly-t.github.io/pg-promise/helpers.html#.update). – vitaly-t Jul 24 '18 at 13:33
  • Thanks. My idea about the where one is that: Where clauses are more diverse then values and set clauses. Values needs separation of keys and/or values by commas, set needs separation of grouped of keys and values by equal sign in between. But wehre is different it needs both determine the comparison operator, not only equal sign, and logic operators. but a payload object lacks those. Defining a func with those as parameters can solve it but it needs to be called multiple times and parameters needed to be detemeined in code time. And all results should be combined. – sçuçu Jul 24 '18 at 15:27
  • For the custom `WHERE` clause you can use [Custom Type Formatting](https://github.com/vitaly-t/pg-promise#custom-type-formatting), and for automated `UPDATE` you can use [helpers.update](http://vitaly-t.github.io/pg-promise/helpers.html#.update). – vitaly-t Jul 24 '18 at 15:27
  • But if one has the knowledge of necessary comparison and logic operators then this can be composed at runtime when request arrives. My ideas is if there is a way that HTTP GET request can send this over as a query stirng with compariosn operators, not only equal signs as it does usually, and the logical operators, not only And (&), then only one fucntion call can transfro this string into a proper where clause. I will look into taht this possibility a bit now. – sçuçu Jul 24 '18 at 15:29
  • Oh Thanks again vitaly-t I will check the custom type formatting better, as well – sçuçu Jul 24 '18 at 15:30
  • I think I did not read it well before – sçuçu Jul 24 '18 at 15:30
  • What about `sets` for `UPDATE`? – sçuçu Jul 24 '18 at 16:05
  • Is there a way to keep it in SQL like we do with `${this:name}` and `{this:csv}` for `INSERT` and `VALUES`, or if not readily available is Custom Type Formatting good fit for that, defining them and using them in SQL queries? – sçuçu Jul 24 '18 at 16:07
  • Of course, you can pre-generate any sub-queries and insert them as raw text. – vitaly-t Jul 24 '18 at 16:08
  • I feel like I need a formatter that can format an enhanced HTTP GET query string I have described above as logic clauese, I mean Where clauses – sçuçu Jul 24 '18 at 16:08
  • @vitaly-t I need more leads on Custom Type Formatting. I am not sure if I do really need it. I need to write something like `name`, `csv` or other formatting construct that you can directly use in SQL without javascript. How do I do that? – sçuçu Jul 26 '18 at 12:24
  • for example I have a query string like that: `entity_id=1&entity_type_id=2`. I can convert these into `job_id = 1 AND working_type_id = 2` to use as a `WHERE` clause. But how can I give this query string to a `db.any` method and have some formatters inside the sql to do the formatting inside sql as your name and csv does. – sçuçu Jul 26 '18 at 12:27
  • When you provide a CTF object as a query-formatting parameter, the query method formats it correctly. And you can test it by using `as.format` to format it manually. – vitaly-t Jul 26 '18 at 12:47
  • I need more examples and more documentation how to do that. For example, how one of the already existing formatter are defined, like `:name`, or `:csv` – sçuçu Jul 27 '18 at 06:28
  • It's all in documentation, see [Filters](https://github.com/vitaly-t/pg-promise#formatting-filters). – vitaly-t Jul 27 '18 at 11:56
  • And if I use that will it internally escape all the strings to prevent SQL injections and do other required things (which I am no expert about but learning) as well? Since the scenario I try to have collects input from the user as query string which I will transform a bit and use as where clause. – sçuçu Aug 01 '18 at 07:13
  • I find that more documentation and examples on this subject is necessary. – sçuçu Aug 10 '18 at 14:43

0 Answers0