emulating table-valued parameters with JSON

[phrrngtn]

This is a just an FYI about something I found cool and interesting. No action is required.

The define extension is a great addition to the list. Really awesome.

While doing some experimentation with lifting of functions between Excel and SQLite and vice versa, I see that it is easy to box a table-valued parameter as JSON and then unbox it in the 'sproc'.

sqlite> create virtual table [L.FUNNY] using define((WITH T(n) AS (SELECT E.value->>0 as n FROM JSON_EACH(:j) AS E) SELECT n FROM T));
sqlite> select * FROM [L.FUNNY]("[[1],[2],[3]]") as F;
1
2
3

sqlite> WITH A AS (
  SELECT json_group_array(json_array(s.value)) as arr 
  FROM generate_series(1,5) as s
) 
SELECT F.* FROM A, [L.FUNNY](A.arr) as F; 
n                                                                                                                                                 
1                                                                                                                                                 
2                                                                                                                                                 
3                                                                                                                                                 
4                                                                                                                                                 
5                                                                                                                                                 

[nalgeon]

Nice trick!