# Utility functions

Utility functions inspect SQL values, format sizes and expose connection information. Some names on this page are table functions: use them in FROM when they produce several rows.

Connection counters refer to the observing SQL connection. Functions that inspect types distinguish the native type descriptor from the legacy runtime value class.

## Example: Inspecting a row

```sql
SELECT num_nonnulls(1, NULL, ''), num_nulls(1, NULL, ''), pg_typeof('hello')
FORMAT csv;
```

```text
2,1,"String"
```

## num_nonnulls

Count non-NULL arguments.

### Syntax

```text
num_nonnulls(value,...)
```

### Arguments

One or more arguments. Only the outer SQL NULL value is counted as missing.

### Returned value

INTEGER number of non-NULL arguments.

### Example

```sql
SELECT num_nonnulls(1,NULL,2) FORMAT csv;
```

```text
2
```

## num_nulls

Count SQL NULL arguments.

### Syntax

```text
num_nulls(value,...)
```

### Arguments

One or more arguments. Native JSON null and collection members are ordinary non-NULL argument values.

### Returned value

INTEGER number of NULL arguments.

### Example

```sql
SELECT num_nulls(1,NULL,2) FORMAT csv;
```

```text
1
```

## pg_size_bytes

Parse a human-readable byte size.

### Syntax

```text
pg_size_bytes(text)
```

### Arguments

At most 512 bytes. Decimal/scientific number plus optional case-insensitive bytes/B/kB/MB/GB/TB/PB. Units use powers of 1024. Round half away from zero; signed 64-bit overflow errors. SQL NULL propagates.

### Returned value

INTEGER bytes.

### Example

```sql
SELECT pg_size_bytes('1.5 MB') FORMAT csv;
```

```text
1572864
```

## pg_size_pretty

Format an exact byte count with binary size units.

### Syntax

```text
pg_size_pretty(bytes)
```

### Arguments

Exact bounded numeric input. Uses PostgreSQL 10 KiB/half-unit thresholds and truncation before half rounding; native precision/scale limits apply. SQL NULL propagates.

### Returned value

TEXT size with bytes/kB/MB/GB/TB/PB.

### Example

```sql
SELECT pg_size_pretty(1048576) FORMAT csv;
```

```text
"1024 kB"
```

## pg_typeof

Describe the bound native type, including typed NULL expressions.

### Syntax

```text
pg_typeof(value)
```

### Arguments

One argument. Bound column/CAST declarations take priority; otherwise infer the present native value. Returns CoordinatorDB type names as text, not PostgreSQL regtype OIDs.

### Returned value

Canonical native declaration, or unknown for an untyped NULL.

### Example

```sql
SELECT pg_typeof(CAST(NULL AS UInt8)) FORMAT csv;
```

```text
"UInt8"
```

## generate_series

Generate inclusive numeric or calendar values as a FROM table with one column.

### Syntax

```text
FROM generate_series(start, stop[, step[, timezone]])
```

### Arguments

Numeric step defaults to 1. Timestamp bounds require a native interval or quantity-unit step. Zero step errors; NULL input and unreachable direction produce no rows. FROM-only; joins use table-function rules.

### Returned value

One generated value per row in column generate_series, in series order.

### Example

```sql
SELECT * FROM generate_series(1,3) FORMAT csv;
```

```text
1
2
3
```

## generate_subscripts

Return one-based indexes of a rectangular native array dimension.

### Syntax

```text
FROM generate_subscripts(array,dimension[,reverse])
```

### Arguments

Positive requested dimension; absent/nonpositive dimension gives no rows; reverse Bool defaults false; ragged arrays error; NULL gives no rows.

### Returned value

One generate_subscripts INTEGER column.

### Example

```sql
SELECT * FROM generate_subscripts(array(10,20),1) FORMAT csv;
```

```text
1
2
```

## to_number

Parse a bounded numeric input mask without rounding through floating point.

### Syntax

```text
to_number(text,template)
```

### Arguments

One to 76 digit positions; 9/0, D/., G/,, S/MI/PL/SG/PR, L/$, V, FM/B, TH and quoted literals. Locale-independent punctuation; templates may contain at most 4096 bytes.

### Returned value

Exact native Decimal128/Decimal256 up to 76 digits; SQL NULL propagates.

### Example

```sql
SELECT CAST(to_number('12,454.8-','99G999D9S') AS TEXT) FORMAT csv;
```

```text
"-12454.8"
```

## unnest

Expand nested native arrays into scalar rows in depth-first order.

### Syntax

```text
FROM unnest(array)
```

### Arguments

One native Array; nested arrays flatten; NULL members emit NULL rows; NULL input and empty arrays emit no rows.

### Returned value

One value per row in column unnest; FROM-only; multiple-array and SELECT-list forms are unsupported.

### Example

```sql
SELECT * FROM unnest(array(1,NULL,2)) FORMAT csv;
```

```text
1
\N
2
```

## Version and connection counters

version() returns the CoordinatorDB server build version and is available before USE. sqlite_version() reports a compatibility version, not the CoordinatorDB build. changes() reports the most recent statement's change count, total_changes() the connection total, and last_insert_rowid() the latest inserted row identifier. Internal catalog and log maintenance preserves these counters.

```sql
SELECT version() FORMAT csv;
```

```text
"0.1.0"
```

## likelihood, likely and unlikely

These functions return their first argument unchanged. likelihood(value,probability) requires a floating-point SQL literal in the range 0 through 1; expressions, integer literals and unary signs are rejected. Parentheses around the literal are accepted. likely(value) and unlikely(value) take one argument.

```sql
SELECT likelihood(7,0.5),likely(7),unlikely(NULL) FORMAT csv;
```

```text
7,7,\N
```

## sqlite_source_id

sqlite_source_id() returns a fixed compatibility source identifier. It does not identify the current CoordinatorDB build. Use version() for the server version.

```sql
SELECT typeof(sqlite_source_id()),length(sqlite_source_id())>0 FORMAT csv;
```

```text
"text",1
```