15 ms·

TIL

gavinray 3y ago
They're incredibly useful -- any time that you want to both: - Reference data from the previous part of the query (the "left-hand side") - Return multiple columns The only way you can achieve it is with LATERAL/CROSS APPLY. Regular correlated subqueries can only return a single column, so something like this doesn't work: SELECT loop.val, (SELECT loop.val * loop.val, 'second column') AS squared FROM (SELECT loop.val FROM generate_series(1,5) AS loop(val)) as loop You'd get: error: subquery must return only one column
- ttfkam 3y ago
  I love LATERALs, but this still fits within set theory and a bulk application rather than an iterative for-loop. It may even be implemented as a for-loop within the engine, but SQL being declarative abstracts that away from the query interface. It's sets all the way down. A set of f(x) is still a set.
  - gavinray 3y ago
    Lets get even more cursed then: CREATE TEMP TABLE temp_results(value int, value_squared int); DO $$ DECLARE r int; BEGIN FOR r IN SELECT generate_series FROM generate_series(1,5) LOOP INSERT INTO temp_results VALUES (r, r * r); END LOOP; END$$; SELECT * FROM temp_results;
    - fifilura 3y ago
      Uw you win, I guess. Already started to think what I would write in that code review comment ;)
      - koolba 3y ago
        At the very least mention that re-running the SELECT in the same connection would include the prior results as well because they are preserved across commands within the same connection.
    - ttfkam 3y ago
      Ha! plpgsql's seemingly sole purpose is to inject imperative code into a set-based environment. Probably does it more smoothly than most pl languages, but that's at the cost of imperative clarity. But you're right. Postgres does allow for-loops like this. (They're also slower than the equivalent set-oriented approach.)
- samstave 3y ago
  If anyone is interested, I had gpt explain this SQL to me - and it was really helpful, as I couldnt parse that with my level of SQL... https://chat.openai.com/share/931b1778-6393-4e86-94b4-b3b5a594917e https://chat.openai.com/share/931b1778-6393-4e86-94b4-b3b5a5...

TIL

They're incredibly useful -- any time that you want to both: - Reference data from the previous part of the query (the "left-hand side") - Return multiple columns The only way you can achieve it is with LATERAL/CROSS APPLY. Regular correlated subqueries can only return a single column, so something like this doesn't work: SELECT loop.val, (SELECT loop.val * loop.val, 'second column') AS squared FROM (SELECT loop.val FROM generate_series(1,5) AS loop(val)) as loop You'd get: error: subquery must return only one column

ttfkam 3y ago
I love LATERALs, but this still fits within set theory and a bulk application rather than an iterative for-loop. It may even be implemented as a for-loop within the engine, but SQL being declarative abstracts that away from the query interface. It's sets all the way down. A set of f(x) is still a set.
- gavinray 3y ago
  Lets get even more cursed then: CREATE TEMP TABLE temp_results(value int, value_squared int); DO $$ DECLARE r int; BEGIN FOR r IN SELECT generate_series FROM generate_series(1,5) LOOP INSERT INTO temp_results VALUES (r, r * r); END LOOP; END$$; SELECT * FROM temp_results;
  - fifilura 3y ago
    Uw you win, I guess. Already started to think what I would write in that code review comment ;)
    - koolba 3y ago
      At the very least mention that re-running the SELECT in the same connection would include the prior results as well because they are preserved across commands within the same connection.
  - ttfkam 3y ago
    Ha! plpgsql's seemingly sole purpose is to inject imperative code into a set-based environment. Probably does it more smoothly than most pl languages, but that's at the cost of imperative clarity. But you're right. Postgres does allow for-loops like this. (They're also slower than the equivalent set-oriented approach.)
samstave 3y ago
If anyone is interested, I had gpt explain this SQL to me - and it was really helpful, as I couldnt parse that with my level of SQL... https://chat.openai.com/share/931b1778-6393-4e86-94b4-b3b5a594917e https://chat.openai.com/share/931b1778-6393-4e86-94b4-b3b5a5...