3 ms·

Tbh I often find I would like to use variables in sql. Something like americans := SELECT username FROM users WHERE country = "us"; SELECT url, visits FROM

by im3w1l 4y ago

Tbh I often find I would like to use variables in sql. Something like americans := SELECT username FROM users WHERE country = "us"; SELECT url, visits FROM posts, americans ON posts.author = americans.username ORDER BY visits DESC LIMIT 10;

hackandthink 4y ago
Common Table Extensions are often good enough: With americans as (select ...) select from ..., americans ... https://www.draxlr.com/blogs/common-table-expressions-and-its-example-in-postgresql/ https://www.draxlr.com/blogs/common-table-expressions-and-it...
- code_biologist 4y ago
  The lack of variables is a major pain point for many devs when learning SQL. CTEs address one aspect. It's maybe not 1:1 or ideal, but lateral joins are a great way to get in-query variables and are extremely powerful for controlling cardinality! https://sqlfordevs.com/for-each-loop-lateral-join https://sqlfordevs.com/for-each-loop-lateral-join
- im3w1l 4y ago
  Oh huh, I didn't know that was a thing. Exactly what I wanted.
bit_for_a_byte 4y ago
It wouldn’t work as a variable, you’d get race conditions unless you explicitly wrapped both query statements in a transaction. It could work as an expression which gets injected into the 2nd query. So the second query would effectively have a nested SELECT statement as defined by the americans expression. But this has the downside that if you reuse americans you have to know that it will be recomputed at each query. So unless you know the semantics of these variable-expression things over time you get race conditions. The solution again would be to wrap everything in a transaction, but at that point you just have to constantly think about transactions, which you don’t if your query is a one-liner. Also good luck writing a query planner that has to efficiently take into account variables.
hyencomper 4y ago
For cases like these, I use sql views - CREATE VIEW view as SELECT * .. Then DROP VIEW view.

by im3w1l 4y ago

hackandthink 4y ago

Common Table Extensions are often good enough: With americans as (select ...) select from ..., americans ... https://www.draxlr.com/blogs/common-table-expressions-and-its-example-in-postgresql/ https://www.draxlr.com/blogs/common-table-expressions-and-it...

code_biologist 4y ago
The lack of variables is a major pain point for many devs when learning SQL. CTEs address one aspect. It's maybe not 1:1 or ideal, but lateral joins are a great way to get in-query variables and are extremely powerful for controlling cardinality! https://sqlfordevs.com/for-each-loop-lateral-join https://sqlfordevs.com/for-each-loop-lateral-join
im3w1l 4y ago
Oh huh, I didn't know that was a thing. Exactly what I wanted.

bit_for_a_byte 4y ago

It wouldn’t work as a variable, you’d get race conditions unless you explicitly wrapped both query statements in a transaction. It could work as an expression which gets injected into the 2nd query. So the second query would effectively have a nested SELECT statement as defined by the americans expression. But this has the downside that if you reuse americans you have to know that it will be recomputed at each query. So unless you know the semantics of these variable-expression things over time you get race conditions. The solution again would be to wrap everything in a transaction, but at that point you just have to constantly think about transactions, which you don’t if your query is a one-liner. Also good luck writing a query planner that has to efficiently take into account variables.

hyencomper 4y ago

For cases like these, I use sql views - CREATE VIEW view as SELECT * .. Then DROP VIEW view.