3 ms·
Even with PL/pgSQL, there is no way to build a SQL statement dynamically _and_ safely -- it's all just string concatenation there, too. However, using `CASE`, `
by solidsnack9000 12y ago
Even with PL/pgSQL, there is no way to build a SQL statement dynamically _and_ safely -- it's all just string concatenation there, too. However, using `CASE`, `WITH` and `LATERAL` you can have a root `SELECT` that returns one of a few different queryable code paths, and `WITH`/`LATERAL` allow you to reuse definitions.
- rpedela 12y agoUse format() for safely constructing dynamic queries. http://www.postgresql.org/docs/9.4/static/functions-string.html#FUNCTIONS-STRING-FORMAT http://www.postgresql.org/docs/9.4/static/functions-string.h...
- solidsnack9000 12y agoYou end up doing a lot of string concatenation even with that, though. It's not quite the same thing as having some kind of literal or combinator based setup. select(from('tab'), where(and(like('name','Mo%'),not(is('id',NULL)))
- rpedela 12y agoYou can get a similar syntax with format() by nesting format() calls such as: EXECUTE format('SELECT * FROM tab %s', format('WHERE %I IS NOT NULL', 'id'));