4 ms·

Thanks, I've frequently wanted a query language that was designed after the 70s. The ideas are sound, but a modernized syntax with variables to reuse subquerie

Thanks, I've frequently wanted a query language that was designed after the 70s. The ideas are sound, but a modernized syntax with variables to reuse subqueries would be lovely. This looks like it. I noticed one issue though... please don't copy the prefix of f-strings! That only exists because Python boxed itself in and it was literally the only ascii syntax left that could be used for string interpolation. It's mildly ugly but the best that could be done given those requirements. Not so here. The way shells do it with single quotes producing literal strings and double quotes available for interpolation has not been topped imho. Triple quotes are a nice extension as well, not sure if that made it in.

aerzen 4y ago
Interesting suggestion. We added f-strings because we already had s-strings (pass trough to SQL) and r-strings (for raw multi-line text). And would you rather see "My {name}" or "My ${name}"? I personally dislike the $ prefix for all variables and interpolations...
- psychoslave 4y ago
  Languages like Perl, Ruby and more offer plethora of additional ways to encode interpolated strings. I especially love the squiggly heredoc for multi line quotations https://infinum.com/blog/multiline-strings-ruby-2-3-0-the-squiggly-heredoc/ https://infinum.com/blog/multiline-strings-ruby-2-3-0-the-sq...
- Kinrany 4y ago
  Choose backticks as the quote style for interpolation to attract Markdown fans and confuse the hell out of MySQL users :D
- digisign 4y ago
  The first one, the $ is redundant if braces required. Multi-line could be triple quoted. SQL, that one I'm not so sure.
oarabbus_ 4y ago
> a modernized syntax with variables to reuse subqueries would be lovely. CTEs provide this functionality already, don't they?
- digisign 4y ago
  When I've needed them I've needed them for multiple statements, once is not enough. Currently have to use plpgsql for this, which is half awesome, half abomination. :-D A single simple language sounds easier to learn.
  - oarabbus_ 4y ago
    I'm not totally sure I follow, as you can re-reference/manipulate the subquery as much as needed. Is it for some kind of dynamic programming like finding a column containing a certain value SELECT cols from table where <ANY_COLUMN> like '%foobar%'" which would need to dynamically insert values into the query select col1 from table where col1 like '%foobar%' union select col2 from table where col2 like '%foobar%' union ... This type of usage is not possible/prohibitively difficult in standard SQL but I'm interested to know if it's a different use-case.
    - digisign 4y ago
      See my comment under the sibling comment.
  - Izkata 4y ago
    Put a comma between them, postgres has been able to do multiple CTEs in a single query for quite some time: https://stackoverflow.com/questions/35248217/multiple-cte-in-single-query https://stackoverflow.com/questions/35248217/multiple-cte-in... Or did you mean like using the same CTE across multiple queries? Views / materialized views are good for that.
    - digisign 4y ago
      The second one, yes. Need to delete from multiple tables with foreign keys back to a single primary table. This before deleting from the primary table, due to consistency. We often get a "list" of pks, then use it in multiple "delete key in" statements. A kludge, but these are for one-off tests on a dev database.

aerzen 4y ago

Interesting suggestion. We added f-strings because we already had s-strings (pass trough to SQL) and r-strings (for raw multi-line text). And would you rather see "My {name}" or "My ${name}"? I personally dislike the $ prefix for all variables and interpolations...

psychoslave 4y ago
Languages like Perl, Ruby and more offer plethora of additional ways to encode interpolated strings. I especially love the squiggly heredoc for multi line quotations https://infinum.com/blog/multiline-strings-ruby-2-3-0-the-squiggly-heredoc/ https://infinum.com/blog/multiline-strings-ruby-2-3-0-the-sq...
Kinrany 4y ago
Choose backticks as the quote style for interpolation to attract Markdown fans and confuse the hell out of MySQL users :D
digisign 4y ago
The first one, the $ is redundant if braces required. Multi-line could be triple quoted. SQL, that one I'm not so sure.

oarabbus_ 4y ago

> a modernized syntax with variables to reuse subqueries would be lovely. CTEs provide this functionality already, don't they?

digisign 4y ago
When I've needed them I've needed them for multiple statements, once is not enough. Currently have to use plpgsql for this, which is half awesome, half abomination. :-D A single simple language sounds easier to learn.
- oarabbus_ 4y ago
  I'm not totally sure I follow, as you can re-reference/manipulate the subquery as much as needed. Is it for some kind of dynamic programming like finding a column containing a certain value SELECT cols from table where <ANY_COLUMN> like '%foobar%'" which would need to dynamically insert values into the query select col1 from table where col1 like '%foobar%' union select col2 from table where col2 like '%foobar%' union ... This type of usage is not possible/prohibitively difficult in standard SQL but I'm interested to know if it's a different use-case.
  - digisign 4y ago
    See my comment under the sibling comment.
- Izkata 4y ago
  Put a comma between them, postgres has been able to do multiple CTEs in a single query for quite some time: https://stackoverflow.com/questions/35248217/multiple-cte-in-single-query https://stackoverflow.com/questions/35248217/multiple-cte-in... Or did you mean like using the same CTE across multiple queries? Views / materialized views are good for that.
  - digisign 4y ago
    The second one, yes. Need to delete from multiple tables with foreign keys back to a single primary table. This before deleting from the primary table, due to consistency. We often get a "list" of pks, then use it in multiple "delete key in" statements. A kludge, but these are for one-off tests on a dev database.