SQL_TVF()¶

Query an external SQL database from within a MatrixOne query using the SQL_TVF() table function. The result schema is supplied at runtime by the second argument, and the connection comes from a sql_tvf_connect() handle or the @sql_tvf_config session variable.

Description¶

SQL_TVF(sql, schema, conn) sends a SQL statement to a foreign SQL database and returns the result rows as a table. The connection is cached per session and reused for the same config.

sql: the SQL text sent verbatim to the source.
schema: a JSON schema spec describing the result columns, or NULL for a schema-less single JSON-array column per row.
conn: a handle from sql_tvf_connect(config), or NULL to use the default connection from @sql_tvf_config.

Syntax¶

SELECT * FROM sql_tvf(sql, schema, conn) t;

SELECT * FROM sql_tvf(sql, schema, NULL) t;   -- default connection
SELECT * FROM sql_tvf(sql, NULL, conn) t;     -- schema-less JSON-array mode

Arguments¶

Argument	Description
`sql`	SQL text sent to the source. Must be a string constant or a bind-time foldable expression.
`schema`	JSON schema spec `{"cols":[{"name":"...","type":"..."}, ...]}` or a short positional spec. `NULL` returns one JSON-array column per row.
`conn`	Connection handle from `sql_tvf_connect(config)`, or `NULL` for the default connection.

The schema type vocabulary is bool, int32, int64, float32, float64, timestamp, and string.

Connection management¶

SET @h = sql_tvf_connect('{"driver":"mysql","dsn":"user:pass@tcp(host:port)/db"}');
SELECT sql_tvf_disconnect(@h);  -- true; a second disconnect returns false

SET @sql_tvf_config = '{"driver":"mysql","dsn":"user:pass@tcp(host:port)/db"}';

sql_tvf_connect(config) opens (or reuses) a connection and returns a handle. The handle is deterministic per config, so reconnecting with the same config reuses the entry. sql_tvf_disconnect(handle) detaches and closes the connection. When the config is omitted or NULL, the default connection from @sql_tvf_config is used. Supported drivers are mysql and postgres.

Examples¶

-- typed schema mode
SELECT * FROM sql_tvf(
  'select id, name from t order by id',
  '{"cols":[{"name":"id","type":"int64"},{"name":"name","type":"string"}]}',
  @h) x;

-- schema-less mode: one JSON-array column per row
SELECT * FROM sql_tvf('select id, name from t order by id', NULL, @h) x;

-- predicates are evaluated by MatrixOne on the returned rows
SELECT count(*) AS c FROM sql_tvf('select id, name from t', '{"cols":[{"name":"id","type":"int64"}]}', @h) x WHERE x.id > 2;

These examples require a reachable foreign SQL database and are not paste-and-run scripts.