parse_dirname

Applies to VAST Cluster 5.5.

Functions › Text basics › Paths. Returns the top-level directory of a file path: its first component, or / for an absolute path. It has no aliases.

Syntax

parse_dirname(path)
parse_dirname(path, separator)

Returns: VARCHAR.

Aliases: none.

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_parse_dirname. 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 table of five file paths, with the top-level directory stored next to each path. One path is a Windows path and one ends with a separator; the last row has no values:

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_parse_dirname"'

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}.files"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, path VARCHAR, top_dir VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '/data/logs/app.log', '/'), "
    "(2, '/home/ada/projects/notes.tar.gz', '/'), "
    "(3, 'C:\\Users\\jose\\poems.docx', 'C:'), "
    "(4, 'exports/2026/', 'exports'), "
    "(5, NULL, NULL)")

[]
[]
[(5,)]

What it does

parse_dirname returns the first component of the path, which is the directory at the top of it. For an absolute path that's the root, /. A path with no separator is a file name with no directory, and gives an empty string:

It's the first element of the parse_path list, except that a path with no separator gives ''. To get every directory above the file, '/data/logs' for '/data/logs/app.log', use parse_dirpath.

The separator argument. With 'forward_slash', only / separates components, so parse_dirname('a\b', 'forward_slash') sees one component and returns ''. 'system' means / on the cluster and gives the same ''. The default, 'backslash', 'both_slash' and a value that isn't a mode name ('x') all split 'a\b' and return 'a'. A NULL separator doesn't make the result NULL: parse_dirname('a/b', NULL) returns 'a'.

In Python code, write each \ of a path as \\ inside the string, so that the SQL receives one backslash. Python prints each backslash in the results doubled.

NULLs and edge cases

The same cases, as the cluster returns them. The third line runs the other separator modes on the same backslash path. They use the table and helpers from Set up the examples:

run("SELECT parse_dirname(NULL), parse_dirname(''), parse_dirname('c.txt'), parse_dirname('/a/b'), parse_dirname('a/b/c.txt')")
run("SELECT parse_dirname('a\\b', 'forward_slash'), parse_dirname('a/b', NULL)")
run("SELECT parse_dirname('a\\b'), parse_dirname('a\\b', 'backslash'), parse_dirname('a\\b', 'both_slash'), "
    "parse_dirname('a\\b', 'system'), parse_dirname('a\\b', 'x')")
run("SELECT parse_dirname(123)")

[(None, '', '', '/', 'a')]
[('', 'a')]
[('a', 'a', 'a', '', 'a')]
InternalError: Binder Error: No function matches the given name and argument types 'parse_dirname(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The top-level directory of each path, next to the full list of components:

run(f"SELECT id, parse_dirname(path), parse_path(path) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '/', ['/', 'data', 'logs', 'app.log']), (2, '/', ['/', 'home', 'ada', 'projects', 'notes.tar.gz']), (3, 'C:', ['C:', 'Users', 'jose', 'poems.docx']), (4, 'exports', ['exports', '2026']), (5, None, None)]

The same paths read with / as the only separator. The Windows path is then a single component:

run(f"SELECT id, parse_dirname(path, 'forward_slash') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '/'), (2, '/'), (3, ''), (4, 'exports'), (5, None)]

Keep only the absolute paths:

run(f"SELECT id, path FROM {TABLE} WHERE parse_dirname(path) = '/' ORDER BY id LIMIT 10")

[(1, '/data/logs/app.log'), (2, '/home/ada/projects/notes.tar.gz')]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so parse_dirname() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes parse_dirname() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes parse_dirname() and keeps the rows that match. No rows are skipped in storage.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE parse_dirname(path) = '/'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│(parse_dirname(path) = '/')│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the top-level directory if you filter on it often. Compute it when you write the rows and keep it in its own column. A comparison of a raw column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan for top_dir = '/' has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE top_dir = '/'")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Combine it with a pushed-down condition. In id > 100 AND parse_dirname(path) = '/', id > 100 is still evaluated in VAST DataBase, so parse_dirname() runs only on the rows that pass it.

Limitations and errors

• Strings only. A number, a decimal, a date, a boolean, a BLOB or a list fails with No function matches the given name and argument types.
• Only the first component. For the whole directory part of a path, use parse_dirpath.
• A file name alone gives '', not NULL. Test with = '' to find paths with no directory.
• Unknown separator names aren't errors. They split on / and \, as the default does. See parse_path.

run("SELECT parse_dirname('\\xAA'::BLOB)")
run("SELECT parse_dirname(DATE '2026-10-16')")

InternalError: Binder Error: No function matches the given name and argument types 'parse_dirname(BLOB)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'parse_dirname(DATE)'. You might need to add explicit type casts.

Similar functions

run("SELECT parse_dirname('/home/ada/notes.txt'), parse_dirpath('/home/ada/notes.txt'), "
    "parse_filename('/home/ada/notes.txt'), parse_path('/home/ada/notes.txt')")
run("SELECT parse_dirname('C:\\dir\\file.tar.gz'), parse_dirpath('C:\\dir\\file.tar.gz'), "
    "parse_filename('C:\\dir\\file.tar.gz'), parse_path('C:\\dir\\file.tar.gz')")

[('/', '/home/ada', 'notes.txt', ['/', 'home', 'ada', 'notes.txt'])]
[('C:', 'C:\\dir', 'file.tar.gz', ['C:', 'dir', 'file.tar.gz'])]

Clean up:

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

[]
[]

See also

• Text basics: all the text functions.
• parse_path: the separator modes in detail.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.