ersatz-table-valued-functions

A bad idea.

ersatz (adj.): made or used as a substitute, typically an inferior one, for something else.

Installation

Install this library using pip:

pip install ersatz-table-valued-functions

Usage

This library lets you rewrite queries like:

SELECT root, square FROM tbl_squares(3)

into queries like:

WITH _ersatz_1 AS (
  SELECT
    JSON_EXTRACT(value, '$[0]') AS "root",
    JSON_EXTRACT(value, '$[1]') AS "square"
  FROM JSON_EACH((SELECT tbl_squares(3)))
)
SELECT root, square FROM _ersatz_1

That is: it translates a query that looks like it needs a table-valued function into one that uses a scalar-valued function that returns a JSON 2D array.

To use it:

from ersatz_table_valued_functions import rewrite

rewrite('SELECT root, square FROM tbl_squares(3)', { 'TBL_SQUARES': ['root', 'square'] })

Development

To contribute to this library, first checkout the code. Then create a new virtual environment:

cd ersatz-table-valued-functions
python -m venv venv
source venv/bin/activate

Now install the dependencies and test dependencies:

pip install -e '.[test]'

To run the tests:

pytest