getvariable

Applies to VAST Cluster 5.5.

Functions › Session and utility functions › Session and diagnostics. Returns the value of the variable with the given name, or NULL when the variable has no value. The name must be a constant string.

Syntax

getvariable('name')

Returns: the variable's type; NULL (of type "NULL") when it has no value.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_string_getvariable. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a small table that holds variable names, to show what happens when the name comes from a column:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_string_getvariable"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.names"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES (1, 'v'), (2, 'w'), (3, NULL)")

[]
[]
[(3,)]

What it does

getvariable(name) looks up a variable by name. If the variable has no value, the result is NULL, and its type is "NULL"; no error is raised. The name must be a constant: a column fails.

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run("SELECT getvariable('v'), getvariable(''), getvariable(NULL), typeof(getvariable('v'))")
run(f"SELECT id, getvariable(name) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT getvariable(123)")
run("SELECT getvariable('v', 'w')")

[(None, None, None, '"NULL"')]
InternalError: Not implemented Error: getvariable requires a constant input
InternalError: Binder Error: No function matches the given name and argument types 'getvariable(INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'getvariable(STRING_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Use a default when the variable has no value:

run("SELECT coalesce(getvariable('v'), 'not set'), getvariable('v') IS NULL")

[('not set', True)]

Use it in a condition on a table. With no value, the comparison is NULL, so no row passes it:

run(f"SELECT id, name FROM {TABLE} WHERE name = getvariable('v') ORDER BY id LIMIT 10")

[]

How it runs in VAST

The Query Engine runs on the cluster's CNodes. getvariable reads a variable of the Query Engine, not data, and its name must be a constant: a column, in the SELECT list, in WHERE or in EXPLAIN, fails with getvariable requires a constant input. With no value, the condition can never be true, and the plan of the condition above has no scan: it's a single EMPTY_RESULT step.

plan(f"SELECT id FROM {TABLE} WHERE name = getvariable('v')")

┌───────────────────────────┐
│        EMPTY_RESULT       │
└───────────────────────────┘

Make it faster

• Nothing to tune for the call itself. It takes only a constant name. What costs time is the rest of the query; see pushdowns.

Limitations and errors

• The name must be a constant. A column fails with getvariable requires a constant input.
• An unknown name isn't an error. It returns NULL, the same as a variable with no value. Check for NULL, or give a default with coalesce.
• One argument only. Two arguments fail with No function matches the given name and argument types.

Similar functions

run("SELECT getvariable('no_such_name_xyz'), current_setting('default_order')")
run("SELECT current_setting('no_such_name_xyz')")

[(None, 'asc')]
InternalError: Catalog Error: unrecognized configuration parameter "no_such_name_xyz"

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• current_setting: read a Query Engine setting.
• Functions: all the function families.