Asterisk syntax and column names as parameter names for dynamic input parameter UDFs

Details

Detail name Value
Changelog Number 27972
Type Improvement
Status Open

Background

Exasol supports UDF scripts with dynamic input parameters using the (...) syntax in CREATE SCRIPT. Within the script the parameter values can be accessed by index. The metadata in the script, e.g., exa.meta.input_columns[i].name in Python, lists the parameter names as 0, 1, 2, etc. See “Details for programming languages” in the user manual for details.

Improvements

We made two improvements:

  • All columns from a table/view can now be passed to a UDF script or a User-Defined Function using the qualified asterisk syntax: SELECT TO_JSON(T.*) FROM T;
    The new syntax can also be combined with other parameters, for example: SELECT TO_JSON(T1., T2.a, T3., CURRENT_TIMESTAMP) FROM T1, T2, T3;
  • UDF scripts with dynamic input parameters can now be created with the flag [USING INPUT COLUMN NAMES]. This makes the fully qualified, delimited names of the columns passed to the script available in the metadata within the script, e.g., in exa.meta.input_columns[i].name in Python. If an expression is passed to the script, the parameter name in the metadata is the parameter index, i.e., the same name as without the new flag. The system tables EXA_ALL_SCRIPTS, EXA_DBA_SCRIPTS, and EXA_USER_SCRIPTS show the usage of this flag in the new column SCRIPT_USING_INPUT_COLUMN_NAMES.

Example

The following script converts the rows of any passed table to valid JSON structures using the column names as keys:

CREATE SCHEMA S;
CREATE TABLE T(ID INT, COUNTRY VARCHAR(100), NAME VARCHAR(100), PHONE_NUMBER_TYPE VARCHAR(100), PHONE_NUMBER VARCHAR(100));
INSERT INTO T VALUES
    (1, 'United Kingdom', 'John Doe', 'private', '+4412345678'),
    (2, 'Germany', 'Hans Meier', 'private', '+4912345678'),
    (2, 'Germany', 'Hans Meier', 'mobile', '+49987654321'),
    (2, 'Germany', 'Achim Schmidt', 'private', '+49214365879'),
    (2, 'Germany', 'Achim Schmidt', 'mobile', '+4989674523');

--/
CREATE PYTHON3 SCALAR SCRIPT TO_JSON(...) [USING INPUT COLUMN NAMES] RETURNS VARCHAR(2000000) AS
import json

def run(ctx):
    return json.dumps(
        {
            str(exa.meta.input_columns[i].name): ctx[i]
            for i in range(exa.meta.input_column_count)
        }
    )
/

SELECT TO_JSON(T.*) FROM T;

Result set:

{"\"S\".\"T\".\"ID\"": 1, "\"S\".\"T\".\"COUNTRY\"": "United Kingdom", "\"S\".\"T\".\"NAME\"": "John Doe", "\"S\".\"T\".\"PHONE_NUMBER_TYPE\"": "private", "\"S\".\"T\".\"PHONE_NUMBER\"": "+4412345678"}
{"\"S\".\"T\".\"ID\"": 2, "\"S\".\"T\".\"COUNTRY\"": "Germany", "\"S\".\"T\".\"NAME\"": "Hans Meier", "\"S\".\"T\".\"PHONE_NUMBER_TYPE\"": "private", "\"S\".\"T\".\"PHONE_NUMBER\"": "+4912345678"}
{"\"S\".\"T\".\"ID\"": 2, "\"S\".\"T\".\"COUNTRY\"": "Germany", "\"S\".\"T\".\"NAME\"": "Hans Meier", "\"S\".\"T\".\"PHONE_NUMBER_TYPE\"": "mobile", "\"S\".\"T\".\"PHONE_NUMBER\"": "+49987654321"}
{"\"S\".\"T\".\"ID\"": 2, "\"S\".\"T\".\"COUNTRY\"": "Germany", "\"S\".\"T\".\"NAME\"": "Achim Schmidt", "\"S\".\"T\".\"PHONE_NUMBER_TYPE\"": "private", "\"S\".\"T\".\"PHONE_NUMBER\"": "+49214365879"}
{"\"S\".\"T\".\"ID\"": 2, "\"S\".\"T\".\"COUNTRY\"": "Germany", "\"S\".\"T\".\"NAME\"": "Achim Schmidt", "\"S\".\"T\".\"PHONE_NUMBER_TYPE\"": "mobile", "\"S\".\"T\".\"PHONE_NUMBER\"": "+4989674523"}