# Built-in Functionality¶

## General¶

We strive to ensure that PipelineDB maintains full compatibility with PostgreSQL 10.1+ and 11.0+. As a result, all of PostgreSQL’s built-in functionality is available to PipelineDB users.

## Aggregates¶

As one of PipelineDB’s fundamental design goals is to **facilitate high-performance continuous aggregation**, PostgreSQL aggregate functions are fully supported for use in Continuous Views (with a couple of rare exceptions). In addition to this large suite of standard aggregates, PipelineDB has also added some of its own Continuous Aggregates that are purpose-built for continuous time-series data processing.

See Continuous Aggregates for more information about some of PipelineDB’s most useful features.

## PipelineDB-specific Types¶

PipelineDB supports a number of native types for efficiently leveraging Probabilistic Data Structures & Algorithms on streams. You’ll likely never need to manually create tables with these types but often they’re the result of Continuous Aggregates, so they’ll be transparently created by Continuous Views. Here they are:

**bloom****cmsketch****topk****hyperloglog****tdigest**

## PipelineDB-specific Functions¶

PipelineDB ships with a number of functions that are useful for interacting with these types. They are described below.

## Bloom Filter Functions¶

**bloom_add ( bloom, expression )**

Adds the given expression to the Bloom Filter.

**bloom_cardinality ( bloom )**

Returns the cardinality of the given Bloom Filter. This is the number of

uniqueelements that were added to the Bloom filter, with a small margin or error.

**bloom_contains ( bloom, expression )**

Returns true if the Bloom filter

probablycontains the given value, with a small false positive rate.

**bloom_intersection ( bloom, bloom, … )**

Returns a Bloom filter representing the intersection of the given Bloom filters.

**bloom_union ( bloom, bloom, … )**

Returns a Bloom filter representing the union of the given Bloom filters.

See Bloom Filter Aggregates for aggregates that can be used to generate Bloom filters.

## Top-K Functions¶

**topk_increment ( topk, expression )**

Increments the frequency of the given expression within the given

topkand returns the resulting Top-K.

**topk_increment ( topk, expression, weight )**

**topk ( topk )**

Returns up to k tuples representing the given Top-K top-k values and their associated frequencies.

**topk_freqs ( topk )**

Returns up to k frequencies associated with the given Top-K top-k most frequent values.

**topk_values ( topk )**

Returns up to k values representing the given Top-K top-k most frequent values.

See Top-K Aggregates for aggregates that can be used to generate **topk** objects.

## Frequency Functions¶

**freq_add ( cmsketch, expression, weight )**

Increments the frequency of the given expression by the specified weight within the given Count-Min Sketch.

**freq ( cmsketch, expression )**

Returns the number of times the value of

expressionwas added to the given Count-Min Sketch, with a small margin of error.

**freq_norm ( cmsketch, expression )**

Returns the normalized frequency of

expressionin the given Count-Min Sketch, with a small margin of error.

**freq_total ( cmsketch )**

Returns the total number of items added to the given Count-Min Sketch.

See Frequency Tracking Aggregates for aggregates that can be used to generate **cmsketches**.

## HyperLogLog Functions¶

**hll_add ( hyperloglog, expression )**

Adds the given expression to the HyperLogLog.

**hll_cardinality ( hyperloglog )**

Returns the cardinality of the given HyperLogLog, with roughly a ~0.2% margin of error.

**hll_union ( hyperloglog, hyperloglog, … )**

Returns a

hyperloglogrepresenting the union of the givenhyperloglog.

See HyperLogLog Aggregates for aggregates that can be used to generate **hyperloglog** objects.

## Distribution Functions¶

**dist_add ( tdigest, expression, weight )**

Increments the frequency of the given expression by the given weight in the T-Digest.

**dist_cdf ( tdigest, expression )**

Given a T-Digest, returns the value of its cumulative-distribution function evaluated at the value of

expression, with a small margin of error.

**dist_quantile ( tdigest, float )**

Given a

tdigest, returns the value at the given quantile,float.floatmust be in`[0, 1]`

.

See Distribution Aggregates for aggregates that can be used to generate **tdigest** objects.

## Miscellaneous Functions¶

**bucket_cardinality ( bucket_agg, bucket_id )**

Returns the cardinality of the given

bucket_idwithin the givenbucket_agg.

**bucket_ids ( bucket_agg )**

Returns an array of all bucket ids contained within the given

bucket_agg.

**bucket_cardinalities ( bucket_agg )**

Returns an array of cardinalities contained within the given

bucket_agg, one for each bucket id.

See Miscellaneous Aggregates for aggregates that can be used to generate **bucket_agg** objects.

**date_round ( timestamp, resolution )**

“Floors” a date down to the nearest

resolution(or bucket) expressed as an interval. This is typically useful for summarization. For example, to summarize events into 10-minute buckets:

```
CREATE VIEW v AS SELECT
date_round(arrival_timestamp, '10 minutes') AS bucket_10m, COUNT(*) FROM stream
GROUP BY bucket_10m;
```

**year ( timestamp )**

Truncate the given timestamp down to its

year.

**month ( timestamp )**

Truncate the given timestamp down to its

month.

**day ( timestamp )**

Truncate the given timestamp down to its

day.

**hour ( timestamp )**

Truncate the given timestamp down to its

hour.

**minute ( timestamp )**

Truncate the given timestamp down to its

minute.

**second ( timestamp )**

Truncate the given timestamp down to its

second.

**set_cardinality ( array )**

Returns the cardinality of the given set array. Sets can be built using

set_agg.

## Operational Functions¶

**pipelinedb.activate ( name )**

Acitvates the given continuous view or transform. See Activation and Deactivation for more information.

**pipelinedb.deactivate ( name )**

Deacitvates the given continuous view or transform. See Activation and Deactivation for more information.

**pipelinedb.combine_table( continuous view name, table )**

Combine the rows from the given

tableinto the given continuous view.combine_tableuses the given continuous view’s query definition to combine aggregate values from both relations with no loss of information.

combine_tablecan be used for purposes such as backfilling a continuous view (possibly running on a completely separate installation) by combining the backfilled rows into the “live” continuous view only once they have been fully populated.

**pipelinedb.get_views ( )**

Returns the set of all continuous views.

**pipelinedb.get_transforms ( )**

Returns the set of all continuous transforms.

**pipelinedb.truncate_continuous_view ( name )**

Truncates all rows from the given continuous view.

**pipelinedb.version ( )**

Returns a string containing all of the version information for your PipelineDB installation.

## System Views¶

PipelineDB includes a number of system views for viewing useful information about your continuous views and transforms:

**pipelinedb.views**

Describes Continuous Views.

```
View "pipelinedb.views"
Column | Type |
-------+---------+
id | oid |
schema | text |
name | text |
active | boolean |
query | text |
```

**pipelinedb.transforms**

Describes Continuous Transforms.

```
View "pipelinedb.transforms"
Column | Type |
-------+---------+
id | oid |
schema | text |
name | text |
active | boolean |
tgfunc | text |
tgargs | text[] |
query | text |
```

**pipelinedb.stream_readers**

For each stream, shows all of the continuous queries that are reading from it.

```
View "pipelinedb.transforms"
Column | Type |
-------------------+-----------+
stream | text |
continuous_queries | text[] |
```

More system views are available for viewing Statistics for PipelineDB processes, continuous queries, and streams.