4 ms·

> 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.
• yellowapple 4y ago
  They're a bit awkward, and I've found that some DBs (cough cough MSSQL cough cough) do a horrific job of optimizing them.