repeat_row

Applies to VAST Cluster 5.5.

Functions › Table functions › Repeated rows. In FROM, repeat_row(a, b, ..., num_rows := n) returns n identical rows, with one column for each value you give it. For a single column, repeat does the same.

Syntax

repeat_row(value1, value2, ..., num_rows := n)

Returns: one column per value, named column0, column1 and so on, of the values' types. Name them with an alias: FROM repeat_row(1, 'x', num_rows := 2) t(a, b).

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_table_functions_repeat_row. 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 an empty table of placeholder orders, which an example fills from repeat_row:

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

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 estimated row count."""
    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).replace(BUCKET, "<bucket>"))

TABLE = f"{SCHEMA}.orders"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, status VARCHAR, placed DATE)")

[]
[]

What it does

repeat_row builds one row from its values and returns it num_rows times:

The values can be of any type and can be expressions: repeat_row(1 + 1, upper('x'), num_rows := 1 + 1) returns (2, 'X') twice. num_rows must be named and must come after the values.

NULLs and edge cases

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

run("SELECT * FROM repeat_row(NULL, num_rows := 1)")
run("SELECT * FROM repeat_row(1, num_rows := 0)")
run("SELECT * FROM repeat_row(1, num_rows := 2.5)")
run("SELECT * FROM repeat_row(1, num_rows := '2')")
run("SELECT * FROM repeat_row(1, num_rows := 2, num_rows := 3)")
run("SELECT * FROM repeat_row(1, 'a')")
run("SELECT * FROM repeat_row(num_rows := 2)")
run("SELECT * FROM repeat_row(num_rows := 2, 1)")
run("SELECT * FROM repeat_row(1, rows := 2)")

[(None,)]
[]
[(1,), (1,), (1,)]
[(1,), (1,)]
[(1,), (1,), (1,)]
InternalError: Binder Error: repeat_rows requires num_rows to be specified
InternalError: Binder Error: repeat_rows requires at least one column to be specified
InternalError: Invalid Error: Unnamed parameters cannot come after named parameters
InternalError: Binder Error: Invalid named parameter "rows" for function repeat_row

Examples

A row of five values of different types, twice, and the column names and types repeat_row gives:

run("SELECT * FROM repeat_row(1, 'a', true, DATE '2026-10-16', [1, 2], num_rows := 2)")
run("SELECT typeof(column0), typeof(column1) FROM repeat_row(1, 'x', num_rows := 1)")

[(1, 'a', True, datetime.date(2026, 10, 16), [1, 2]), (1, 'a', True, datetime.date(2026, 10, 16), [1, 2])]
[('INTEGER', 'VARCHAR')]

Name the columns with an alias, and compute the values:

run("SELECT a, b FROM repeat_row(1, 'x', num_rows := 2) t(a, b)")
run("SELECT * FROM repeat_row(1 + 1, upper('x'), num_rows := 1 + 1)")

[(1, 'x'), (1, 'x')]
[(2, 'X'), (2, 'X')]

Add three placeholder orders to a table in one statement, then read them:

run(f"INSERT INTO {TABLE} SELECT * FROM repeat_row(0, 'placeholder', DATE '2026-10-17', num_rows := 3)")
run(f"SELECT id, status, placed FROM {TABLE} LIMIT 10")

[(3,)]
[(0, 'placeholder', datetime.date(2026, 10, 17)), (0, 'placeholder', datetime.date(2026, 10, 17)), (0, 'placeholder', datetime.date(2026, 10, 17))]

Count a large number of rows:

run("SELECT count(*) FROM repeat_row(1, 'x', num_rows := 100000)")

[(100000,)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes. A query on a table starts with a scan in VAST DataBase (VAST_SCAN); repeat_row replaces that scan. It produces its rows in the Query Engine, and the plan shows a REPEAT_ROW step where VAST_SCAN would be. Nothing is read from storage:

plan("SELECT * FROM repeat_row(1, num_rows := 3)")

┌───────────────────────────┐
│        REPEAT_ROW         │
│    ────────────────────   │
│    Function: REPEAT_ROW   │
│                           │
└───────────────────────────┘

Make it faster

• Generate rows on the cluster. INSERT INTO t SELECT * FROM repeat_row(..., num_rows := n) creates the rows in the Query Engine instead of sending them from your client. Give one value per column of the table, in the table's column order.
• For rows that differ, use a series. repeat_row returns identical rows. To number them, build them from range instead: SELECT x, 'placeholder' FROM range(3) t(x).

Limitations and errors

• num_rows is required and must be named. See NULLs and edge cases for the errors.
• num_rows must be 0 or more and not NULL. A negative or NULL value fails with an internal error.
• One source per query. Like the other table functions, repeat_row can't be joined with a table or with another table function (table functions).
• ORDER BY needs a LIMIT. Without one it fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.

run("SELECT * FROM repeat_row(1, 'x', num_rows := 3) t(a, b) ORDER BY a")
run("SELECT * FROM repeat_row(1, 'x', num_rows := 3) t(a, b) ORDER BY a LIMIT 2")

InternalError: The operator 'ORDER_BY' type=1 is currently not supported for this usage.
[(1, 'x'), (1, 'x')]

Similar functions

run("SELECT * FROM repeat_row('x', 1, num_rows := 2)")
run("SELECT * FROM repeat('x', 2)")
run("SELECT * FROM range(2)")

[('x', 1), ('x', 1)]
[('x',), ('x',)]
[(0,), (1,)]

Clean up:

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

[]
[]

See also

• Table functions: all the table functions.
• repeat, range and unnest.
• INSERT and SELECT.
• EXPLAIN: how to read a plan.