Show HN: Natural-SQL-7B, a strong text-to-SQL model

Re:Show HN: Natural-SQL-7B, a strong text-to-SQL model by gavinray on Monday February 05, 2024 @11:49AM (#39263305)

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

[ Parent | on HN ]

Re:Show HN: Natural-SQL-7B, a strong text-to-SQL model by ttfkam on Monday February 05, 2024 @11:59AM (#39263445)

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.

[ Parent | on HN ]

Re:Show HN: Natural-SQL-7B, a strong text-to-SQL model by gavinray on Monday February 05, 2024 @12:07PM (#39263559)

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;

[ Parent | on HN ]

Re:Show HN: Natural-SQL-7B, a strong text-to-SQL model by fifilura on Monday February 05, 2024 @12:27PM (#39263805)

Uw you win, I guess. Already started to think what I would write in that code review comment ;)

[ Parent | on HN ]

Re:Show HN: Natural-SQL-7B, a strong text-to-SQL model by koolba on Monday February 05, 2024 @03:12PM (#39266452)

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.

[ Parent | on HN ]

Re:Show HN: Natural-SQL-7B, a strong text-to-SQL model by ttfkam on Monday February 05, 2024 @03:05PM (#39266361)

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.)

[ Parent | on HN ]

Re:Show HN: Natural-SQL-7B, a strong text-to-SQL model by samstave on Monday February 05, 2024 @12:00PM (#39263455)

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-b3b5a5...

[ Parent | on HN ]