repeat

Applies to VAST Cluster 5.5.

Functions › Text basics › Changing text. Returns a string, a BLOB or a list repeated a number of times, joined end to end. repeat has no aliases.

Syntax

repeat(s, count)

Returns: the type of s: VARCHAR, BLOB, or a list of the same element type.

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_repeat. 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 four tasks in an outline: each has a depth, a list of tags and a marker byte string. 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_repeat"'

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}.tasks"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, title VARCHAR, depth BIGINT, tags VARCHAR[], marker BLOB)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Plan the release', 0, ['planning'], '\\xAA'::BLOB), "
    "(2, 'Write the notes', 1, ['docs', 'release'], '\\x00\\xFF'::BLOB), "
    "(3, 'Review the notes', 2, [], ''::BLOB), "
    "(4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

repeat joins count copies of s end to end. It has one form for each type of s:

On a list, repeat repeats the elements, not the list: the result is one flat list, not a list of lists. A count of 0 or less returns an empty value of the same type: '', an empty BLOB or an empty list (repeat([1, 2], 0) and repeat([1], -1) both return []). An empty list or an empty BLOB stays empty. The result can be large: repeat('ab', 1000000) returns a string of 2,000,000 characters.

repeat used in a FROM clause is a different function with the same name: a table function that returns a value in a number of rows. See Table functions.

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 repeat(NULL, 2), repeat('ab', NULL), repeat('', 2), repeat('', 5), repeat('ab', 0), repeat('ab', -1)")
run("SELECT typeof(repeat(NULL, 2)), typeof(repeat(CAST(NULL AS VARCHAR), 2))")
run("SELECT repeat('\\xAA'::BLOB, 2), repeat([1, 2], 2), repeat(['a', 'b'], 2), length(repeat('ab', 1000000))")
run("SELECT repeat('ab', 2.6)")
run("SELECT repeat(123, 2)")

[(None, None, '', '', '', '')]
[('BLOB', 'VARCHAR')]
[(b'\xaa\xaa', [1, 2, 1, 2], ['a', 'b', 'a', 'b'], 2000000)]
InternalError: Binder Error: No function matches the given name and argument types 'repeat(STRING_LITERAL, DECIMAL(2,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'repeat(INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Indent each title by its depth, two dots per level. A depth of 0 gives an empty indent, and the row with no values gives NULL:

run(f"SELECT id, repeat('..', depth) || title FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'Plan the release'), (2, '..Write the notes'), (3, '....Review the notes'), (4, None)]

Repeat a list and a BLOB column. Each result keeps the type of its column: a list of VARCHAR and a BLOB:

run(f"SELECT id, repeat(tags, 2), repeat(marker, 3) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, ['planning', 'planning'], b'\xaa\xaa\xaa'), (2, ['docs', 'release', 'docs', 'release'], b'\x00\xff\x00\xff\x00\xff'), (3, [], b''), (4, None, None)]

Keep the tasks whose indent is two levels deep. Make it faster shows a cheaper way to write this filter:

run(f"SELECT id, title FROM {TABLE} WHERE repeat('..', depth) = '....' ORDER BY id LIMIT 10")

[(3, 'Review the notes')]

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 repeat() is always computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine computes repeat() for each row it returns.
• In WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine computes repeat() 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 repeat('..', depth) = '....'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (repeat('..', depth) = '..│
│            ..')           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           depth           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter on the count, not on the repeated value. repeat('..', depth) = '....' keeps the same rows as depth = 2, but only the second is a comparison of a raw column with a constant, which is pushed down to VAST DataBase, so rows that don't match never leave storage. Its plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE depth = 2")

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

• Mind the size of the result. The result is count times the size of s, for every row. A large count on a long column makes every returned row that much larger.

Limitations and errors

• Strings, BLOB values and lists only. A number, a date or a boolean fails with No function matches the given name and argument types. Cast it to VARCHAR first.
• An integer count. A decimal count, such as 2.6, fails with No function matches the given name and argument types. Cast it to BIGINT first; the cast rounds, so CAST(2.6 AS BIGINT) repeats 3 times.
• An untyped NULL gives a BLOB. repeat(NULL, 2) uses the BLOB form. Cast the NULL if the type matters: repeat(CAST(NULL AS VARCHAR), 2).
• space isn't available. space(n) fails with Scalar Function with name space does not exist!. Use repeat(' ', n).

run("SELECT repeat('ab', CAST(2.6 AS BIGINT)), repeat(' ', 3) || '|'")
run("SELECT space(3)")

[('ababab', '   |')]
InternalError: Catalog Error: Scalar Function with name space does not exist!

Similar functions

The list and BLOB forms are also described in List functions and Binary functions. To pad a string to a given length, use lpad and rpad. replace, translate and reverse are the other functions that change text.

run("SELECT repeat('ab', 2), repeat([1, 2], 2), repeat('\\xAA'::BLOB, 2)")
run("SELECT * FROM repeat(42, 2)")

[('abab', [1, 2, 1, 2], b'\xaa\xaa')]
[(42,), (42,)]

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.
• Table functions: repeat in FROM.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.