On Sun, Aug 23, 2015 at 10:55 PM, Richard Hipp <drh at sqlite.org> wrote:
> Regular SQL functions always return scalars in SQLite. > > See https://www.sqlite.org/src/artifact/b8fb7befd85b3a9b for an > example of how to implement table-valued functions. This is a new > feature so there is no documentation on it yet. But the example is > well-commented. This capability will be in the next release, so > you'll have to compile from trunk if you want to use it right away - > it is not found in 3.8.11.1. > The example only uses literals for the function's arguments. Can columns coming from a join be used as well? A canonical use-case would be to list values of a JSON-formatted text columns, like the following made-up SQL: create table t (id INTEGER PRIMARY KEY, json TEXT); insert into t values (1, '[1]'), (2, '[]'), (3,'[3,4]); select id, j.column_value as json_array_entry from t, array_values(t.json) j; id | json_array_entry 1 | 1 3 | 3 3 | 4 See also https://docs.oracle.com/database/121/ADXDB/json.htm and http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns004.htm Thanks, --DD
