Ability to detect & use variables in SQL Query

[JacobWeyer]

It'd be interesting if Postico could have a slide down menu that could detect variables in the sequel query. EX. if the query is SELECT * FROM table WHERE id = ${id} and it see's the ${id} in the string, it'd be able to show that as a variable we can change and that'd inject it into the query without having to change my variables to test sql queries when writing more complex ones.

[jakob]

Good idea, but what language is this? The usual placeholders are ? (Rails) or $1 (libpq) or :id (PHP PDO).

[JacobWeyer]

You're right, I'm using pg-promise library and the ${Id} is actually a JavaScript placeholder.

On Thu, Mar 9, 2017, 3:02 AM Jakob Egger [email protected] wrote:

Good idea, but what language is this? The usual placeholders are ? (Rails) or $1 (libpq) or :id (PHP PDO).

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/jakob/Postico/issues/339#issuecomment-285281747, or mute the thread https://github.com/notifications/unsubscribe-auth/AIpIJsa8l_ktNOIIgBTdWnEELFNK4DbKks5rj7ITgaJpZM4MW-0p .

[doekman]

This probably should work like psql's variables, so you could also use special variables like DBNAME.

Basically the most imporant one, for queries like GRANT CONNECT ON DATABASE :DBNAME TO my_new_role;, so you don't need to hard-code the database name in SQL. I use the extension .psql for this, since this tool supports it.

I guess this is the same as issue #297 ?

[KurtPattyn]

The same functionality as in DBeaver would already suit me

[doekman]

The nice thing about psql's variables is the interpolation. One could write this:

SELECT :'custom_name' as "name", 
       :"custom_column" as "value" 
FROM some_table"`

And psql will insert the value of the variable custom_name as string literal (single quotes), and custom_column as identifier literal (double quotes). Pretty handy...

[jakob]

Good point.

There's a small typo in your example: According to the docs, the syntax is :name, :'name', or :"name"

[doekman]

Good catch. Fixed.