4 ms·
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 abomina
by 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 agoI'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 agoSee my comment under the sibling comment.
- Izkata 4y agoPut 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 agoThe 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.