---
layout: default
title: Table-Valued Functions
parent: Functions
nav_order: 4
---

# Table-Valued Functions

Table-valued functions (TVFs) are functions that return a set of rows rather than a single value. They are used in the `FROM` clause of a query, just like a regular table. Oxibase supports TVFs with full SQL integration: WHERE filtering, ORDER BY, LIMIT, JOINs, subqueries, CTEs, and aggregation all work seamlessly.

## GENERATE_SERIES

Generates a series of values from `start` to `stop` (inclusive) with an optional `step`. Supports integer, float, and timestamp/date types.

### Syntax

```sql
GENERATE_SERIES(start, stop)
GENERATE_SERIES(start, stop, step)
```

**Parameters:**

| Parameter | Description |
|-----------|-------------|
| `start` | The first value in the series |
| `stop` | The last value in the series (inclusive) |
| `step` | The increment between values (optional, auto-detected if omitted) |

**Return value:** A table with a single column named `value`.

### Integer Series

Generate a sequence of integers.

```sql
-- Basic ascending series
SELECT * FROM generate_series(1, 5);
-- Returns: 1, 2, 3, 4, 5

-- With explicit step
SELECT * FROM generate_series(0, 10, 2);
-- Returns: 0, 2, 4, 6, 8, 10

-- Descending with negative step
SELECT * FROM generate_series(5, 1, -1);
-- Returns: 5, 4, 3, 2, 1

-- Auto-detect descending (no step needed)
SELECT * FROM generate_series(5, 1);
-- Returns: 5, 4, 3, 2, 1

-- Negative range
SELECT * FROM generate_series(-3, 3);
-- Returns: -3, -2, -1, 0, 1, 2, 3

-- Single value (start equals stop)
SELECT * FROM generate_series(3, 3);
-- Returns: 3
```

### Float Series

When any argument is a float, the function generates floating-point values. Index-based generation is used internally to avoid floating-point drift.

```sql
-- Float series with fractional step
SELECT * FROM generate_series(0.0, 1.0, 0.5);
-- Returns: 0.0, 0.5, 1.0

-- Mixed integer and float arguments
SELECT * FROM generate_series(0, 2, 0.5);
-- Returns: 0.0, 0.5, 1.0, 1.5, 2.0
```

### Timestamp/Date Series

Generate a sequence of timestamps or dates. The `start` and `stop` values can be date strings (`'YYYY-MM-DD'`) or timestamp strings (`'YYYY-MM-DD HH:MM:SS'`). The `step` is an interval string.

**Supported interval units:**

| Unit | Examples |
|------|----------|
| year/years | `'1 year'`, `'2 years'` |
| month/months | `'1 month'`, `'3 months'` |
| week/weeks | `'1 week'`, `'2 weeks'` |
| day/days | `'1 day'`, `'7 days'` |
| hour/hours | `'1 hour'`, `'6 hours'` |
| minute/minutes/min | `'1 minute'`, `'30 min'` |
| second/seconds/sec | `'1 second'`, `'15 sec'` |
| millisecond/milliseconds/ms | `'100 ms'` |
| microsecond/microseconds/us | `'500 us'` |

```sql
-- Daily series
SELECT * FROM generate_series('2024-01-01', '2024-01-05', '1 day');
-- Returns: 2024-01-01, 2024-01-02, 2024-01-03, 2024-01-04, 2024-01-05

-- Auto-detect daily step
SELECT * FROM generate_series('2024-01-01', '2024-01-03');
-- Returns: 2024-01-01, 2024-01-02, 2024-01-03

-- Hourly series
SELECT * FROM generate_series(
    '2024-01-01 00:00:00',
    '2024-01-01 06:00:00',
    '2 hours'
);
-- Returns: 00:00, 02:00, 04:00, 06:00

-- Every 10 minutes
SELECT * FROM generate_series(
    '2024-01-01 00:00:00',
    '2024-01-01 00:30:00',
    '10 minutes'
);
-- Returns: 00:00, 00:10, 00:20, 00:30

-- Weekly series
SELECT * FROM generate_series('2024-01-01', '2024-01-29', '1 week');
-- Returns: Jan 1, Jan 8, Jan 15, Jan 22, Jan 29

-- Monthly series (1 month = 30 days)
SELECT * FROM generate_series('2024-01-01', '2024-04-01', '1 month');

-- Descending date series
SELECT * FROM generate_series('2024-01-05', '2024-01-01', '-1 day');
-- Returns: Jan 5, Jan 4, Jan 3, Jan 2, Jan 1
```

### Column Aliases

The default output column is named `value`. You can rename it using standard SQL aliasing.

```sql
-- Table alias with column alias
SELECT n FROM generate_series(1, 5) AS gs(n);

-- Implicit alias (without AS keyword)
SELECT n FROM generate_series(1, 5) gs(n);

-- Using default column name
SELECT value FROM generate_series(1, 5);
```

### Scalar Mode

When used in a `SELECT` expression (without `FROM`), `GENERATE_SERIES` returns a JSON array string. This matches DuckDB behavior.

```sql
SELECT generate_series(1, 5);
-- Returns: '[1, 2, 3, 4, 5]'

SELECT generate_series(0, 10, 2);
-- Returns: '[0, 2, 4, 6, 8, 10]'

SELECT generate_series('2024-01-01', '2024-01-03', '1 day');
-- Returns: '["2024-01-01T00:00:00+00:00", "2024-01-02T00:00:00+00:00", "2024-01-03T00:00:00+00:00"]'
```

## Using with SQL Clauses

### WHERE

```sql
SELECT * FROM generate_series(1, 10) AS g(value)
WHERE value > 7;
-- Returns: 8, 9, 10

SELECT * FROM generate_series('2024-01-01', '2024-01-10', '1 day') AS g(value)
WHERE value > '2024-01-07';
-- Returns: Jan 8, Jan 9, Jan 10
```

### ORDER BY

```sql
SELECT * FROM generate_series(1, 5) AS g(value)
ORDER BY value DESC;
-- Returns: 5, 4, 3, 2, 1
```

### LIMIT and OFFSET

```sql
SELECT * FROM generate_series(1, 100) AS g(value)
LIMIT 5;
-- Returns: 1, 2, 3, 4, 5

-- LIMIT with OFFSET using CTE
WITH gs AS (
    SELECT * FROM generate_series(1, 10) AS g(value)
)
SELECT * FROM gs LIMIT 3 OFFSET 2;
-- Returns: 3, 4, 5
```

### Aggregation

```sql
-- Sum of 1 to 100
SELECT SUM(value) FROM generate_series(1, 100) AS g(value);
-- Returns: 5050

-- Count
SELECT COUNT(*) FROM generate_series(1, 1000) AS g(value);
-- Returns: 1000

-- Count days in a year
SELECT COUNT(*) FROM generate_series('2024-01-01', '2024-12-31', '1 day') AS g(value);
-- Returns: 366 (2024 is a leap year)
```

### JOINs

```sql
-- Join with a regular table
SELECT g.n, t.name
FROM generate_series(1, 3) AS g(n)
JOIN users t ON g.n = t.id
ORDER BY g.n;

-- Cross join two series (generates a grid)
SELECT a.value AS x, b.value AS y
FROM generate_series(1, 3) AS a(value)
CROSS JOIN generate_series(1, 2) AS b(value)
ORDER BY x, y;
-- Returns: (1,1), (1,2), (2,1), (2,2), (3,1), (3,2)
```

### Subqueries and CTEs

```sql
-- In a subquery
SELECT * FROM (
    SELECT * FROM generate_series(1, 5) AS g(n)
) sub
ORDER BY n;

-- In a CTE
WITH numbers AS (
    SELECT value FROM generate_series(1, 10) AS g(value)
)
SELECT value, value * value AS squared
FROM numbers
WHERE value <= 5;
```

## Practical Examples

### Generate a Calendar Table

```sql
SELECT value AS date
FROM generate_series('2024-01-01', '2024-12-31', '1 day') AS g(value)
ORDER BY date;
```

### Generate Hourly Time Slots

```sql
SELECT value AS slot
FROM generate_series(
    '2024-01-01 08:00:00',
    '2024-01-01 17:00:00',
    '1 hour'
) AS g(value);
```

### Fill Gaps in Time Series Data

```sql
-- Generate all dates, then left join with actual data
WITH dates AS (
    SELECT value AS date
    FROM generate_series('2024-01-01', '2024-01-31', '1 day') AS g(value)
)
SELECT d.date, COALESCE(o.total, 0) AS total
FROM dates d
LEFT JOIN daily_orders o ON d.date = o.order_date
ORDER BY d.date;
```

### Number Table for Testing

```sql
-- Generate test IDs
INSERT INTO test_data (id, value)
SELECT value, value * 10
FROM generate_series(1, 100) AS g(value);
```

### Multiplication Table

```sql
SELECT a.value AS x, b.value AS y, a.value * b.value AS product
FROM generate_series(1, 10) AS a(value)
CROSS JOIN generate_series(1, 10) AS b(value)
ORDER BY x, y;
```

## Behavior Notes

- **Inclusive bounds**: Both `start` and `stop` are included in the output when the step aligns.
- **Auto-detect direction**: When `step` is omitted, the function automatically determines the direction. For integers and floats, it uses `+1` or `-1`. For timestamps, it uses `+1 day` or `-1 day`.
- **Direction mismatch**: If the step goes in the opposite direction from start to stop (e.g., `generate_series(1, 5, -1)`), an empty result is returned. This follows PostgreSQL behavior.
- **Zero step**: A step of zero is an error.
- **Safety limit**: A maximum of 10,000,000 rows can be generated per call to prevent out-of-memory conditions.
- **Case insensitive**: `GENERATE_SERIES`, `generate_series`, and `Generate_Series` all work.
- **Month approximation**: The `month` interval unit is approximated as 30 days. For exact calendar month arithmetic, use application-level logic.
