Enum PgFunc 

#[non_exhaustive]
pub enum PgFunc {
    ToTsquery,
    ToTsvector,
    PhrasetoTsquery,
    PlaintoTsquery,
    WebsearchToTsquery,
    TsRank,
    TsRankCd,
    StartsWith,
    GenRandomUUID,
    JsonBuildObject,
    JsonAgg,
    ArrayAgg,
    DateTrunc,
    Any,
    Some,
    All,
}

Known Postgres-specific functions.

For all supported functions (including the standard ones), see Function.

If something is not supported, you can use Function::Custom.

Variants (Non-exhaustive)

Non-exhaustive enums could have additional variants added in future. Therefore, when matching against variants of non-exhaustive enums, an extra wildcard arm must be added to account for any future variants.

ToTsquery

ToTsvector

PhrasetoTsquery

PlaintoTsquery

WebsearchToTsquery

TsRank

TsRankCd

StartsWith

GenRandomUUID

JsonBuildObject

JsonAgg

ArrayAgg

DateTrunc

Any

Some

All

Implementations

impl PgFunc

pub fn json_exists<C, P>(context_item: C, path_expression: P) -> Builder

where C: Into<Expr>, P: Into<Cow<'static, str>>,

Create a JSON_EXISTS function builder. Postgres only.

Examples

Basic usage with PASSING parameters:

use sea_query::extension::postgres::*;
use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(
        PgFunc::json_exists(
           Expr::cust(r#"jsonb '{"key1": [1,2,3]}'"#),
            "strict $.key1[*] ? (@ > $x)",
        )
        .passing(2, "x"),
    )
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT JSON_EXISTS(jsonb '{"key1": [1,2,3]}' 'strict $.key1[*] ? (@ > $x)' PASSING 2 AS x)"#
);

With ERROR ON ERROR clause:

use sea_query::extension::postgres::*;
use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(
        PgFunc::json_exists(Expr::cust(r#"jsonb '{"a": [1,2,3]}'"#), "lax $.a[5]")
            .error_on_error(),
    )
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT JSON_EXISTS(jsonb '{"a": [1,2,3]}' 'lax $.a[5]' ERROR ON ERROR)"#
);

With strict mode and ERROR ON ERROR:

use sea_query::extension::postgres::*;
use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(
        PgFunc::json_exists(Expr::cust(r#"jsonb '{"a": [1,2,3]}'"#), "strict $.a[5]")
            .error_on_error(),
    )
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT JSON_EXISTS(jsonb '{"a": [1,2,3]}' 'strict $.a[5]' ERROR ON ERROR)"#
);

impl PgFunc

pub fn json_query<T, P>(context_item: T, path_expression: P) -> Builder

where T: Into<Expr>, P: Into<Cow<'static, str>>,

Create a JSON_QUERY function builder. Postgres only.

Examples

Basic usage:

use sea_query::extension::postgres::*;
use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(
        PgFunc::json_query(Expr::cust(r#"jsonb '[1,[2,3],null]'"#), "lax $[*][$off]")
            .passing(1, "off")
            .wrapper(json_fn::WrapperKind::WithConditional),
    )
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT JSON_QUERY(jsonb '[1,[2,3],null]' 'lax $[*][$off]' PASSING 1 AS off WITH CONDITIONAL WRAPPER)"#
);

With OMIT QUOTES:

use sea_query::extension::postgres::*;
use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(
        PgFunc::json_query(Expr::cust(r#"jsonb '{"a": "[1, 2]"}'"#), "lax $.a")
            .quotes(json_fn::QuotesKind::Omit),
    )
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT JSON_QUERY(jsonb '{"a": "[1, 2]"}' 'lax $.a' OMIT QUOTES)"#
);

impl PgFunc

pub fn json_table<T, P>(context_item: T, path_expression: P) -> Builder

where T: Into<Expr>, P: Into<Cow<'static, str>>,

Create a JSON_TABLE function builder. Postgres only.

Examples

Basic usage with simple columns:

use sea_query::extension::postgres::*;
use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from_function(
        PgFunc::json_table(
            Expr::val(r#"[{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]"#),
            "$[*]"
        )
        .ordinality_column("row_number")
        .column("name", "text").path("$.name").build_column()
        .column("age", "int").path("$.age").build_column()
        .build(),
        "people"
    )
    .column("*")
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "*" FROM JSON_TABLE('[{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]', '$[*]' COLUMNS (row_number FOR ORDINALITY, name "text" PATH '$.name', age "int" PATH '$.age')) AS "people""#
);

With PASSING parameters and error handling:

use sea_query::extension::postgres::*;
use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from_function(
        PgFunc::json_table(
            Expr::val(r#"{"users": [{"id": 1, "name": "John"}, {"id": 2, "name": "Jane"}]}"#),
            "$.users[*] ? (@.id > $min_id)"
        )
        .passing(0, "min_id")
        .column("user_id", "int").path("$.id").null_on_error().build_column()
        .column("user_name", "text").path("$.name").default_on_empty(Expr::val("Unknown")).build_column()
        .error_on_error()
        .build(),
        "filtered_users"
    )
    .column("*")
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "*" FROM JSON_TABLE('{"users": [{"id": 1, "name": "John"}, {"id": 2, "name": "Jane"}]}', '$.users[*] ? (@.id > $min_id)' PASSING 0 AS min_id COLUMNS (user_id "int" PATH '$.id' NULL ON ERROR, user_name "text" PATH '$.name' DEFAULT 'Unknown' ON EMPTY) ERROR ON ERROR) AS "filtered_users""#
);

With NESTED PATH:

use sea_query::extension::postgres::*;
use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from_function(
        PgFunc::json_table(
            Expr::val(r#"{"users": [{"name": "John", "phones": ["123", "456"]}, {"name": "Jane", "phones": ["789"]}]}"#),
            "$.users[*]"
        )
        .column("user_name", "text").path("$.name").build_column()
        .nested("$.phones[*]")
            .column("phone", "text").path("$").build_column()
            .build_nested()
        .build(),
        "user_phones"
    )
    .column("*")
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "*" FROM JSON_TABLE('{"users": [{"name": "John", "phones": ["123", "456"]}, {"name": "Jane", "phones": ["789"]}]}', '$.users[*]' COLUMNS (user_name "text" PATH '$.name', NESTED '$.phones[*]' COLUMNS (phone "text" PATH '$'))) AS "user_phones""#
);

impl PgFunc

pub fn json_value<T, P>(context_item: T, path_expression: P) -> Builder

where T: Into<Expr>, P: Into<Cow<'static, str>>,

Create a JSON_VALUE function builder. Postgres only.

Examples

Basic usage with RETURNING:

use sea_query::extension::postgres::*;
use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(
        PgFunc::json_value(Expr::cust(r#"jsonb '"123.45"'"#), "$")
            .returning("float")
            .build(),
    )
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT JSON_VALUE(jsonb '"123.45"' '$' RETURNING "float")"#
);

With PASSING parameters:

use sea_query::extension::postgres::*;
use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(
        PgFunc::json_value(Expr::cust(r#"jsonb '[1,2]'"#), "strict $[$off]")
            .passing(1, "off")
            .build(),
    )
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT JSON_VALUE(jsonb '[1,2]' 'strict $[$off]' PASSING 1 AS off)"#
);

With DEFAULT ON ERROR:

use sea_query::extension::postgres::*;
use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(
        PgFunc::json_value(Expr::val(r#"[1,2]"#), "strict $[*]").default_on_error(Expr::val(9)),
    )
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT JSON_VALUE('[1,2]' 'strict $[*]' DEFAULT 9 ON ERROR)"#
);

impl PgFunc

pub fn to_tsquery<T>(expr: T, regconfig: Option<u32>) -> FunctionCall

where T: Into<Expr>,

Call TO_TSQUERY function. Postgres only.

The parameter regconfig represents the OID of the text search configuration. If the value is None the argument is omitted from the query, and hence the database default used.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(PgFunc::to_tsquery("a & b", None))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT TO_TSQUERY('a & b')"#
);

pub fn to_tsvector<T>(expr: T, regconfig: Option<u32>) -> FunctionCall

where T: Into<Expr>,

Call TO_TSVECTOR function. Postgres only.

The parameter regconfig represents the OID of the text search configuration. If the value is None the argument is omitted from the query, and hence the database default used.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(PgFunc::to_tsvector("a b", None))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT TO_TSVECTOR('a b')"#
);

pub fn phraseto_tsquery<T>(expr: T, regconfig: Option<u32>) -> FunctionCall

where T: Into<Expr>,

Call PHRASE_TO_TSQUERY function. Postgres only.

The parameter regconfig represents the OID of the text search configuration. If the value is None the argument is omitted from the query, and hence the database default used.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(PgFunc::phraseto_tsquery("a b", None))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT PHRASETO_TSQUERY('a b')"#
);

pub fn plainto_tsquery<T>(expr: T, regconfig: Option<u32>) -> FunctionCall

where T: Into<Expr>,

Call PLAIN_TO_TSQUERY function. Postgres only.

The parameter regconfig represents the OID of the text search configuration. If the value is None the argument is omitted from the query, and hence the database default used.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(PgFunc::plainto_tsquery("a b", None))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT PLAINTO_TSQUERY('a b')"#
);

pub fn websearch_to_tsquery<T>(expr: T, regconfig: Option<u32>) -> FunctionCall

where T: Into<Expr>,

Call WEBSEARCH_TO_TSQUERY function. Postgres only.

The parameter regconfig represents the OID of the text search configuration. If the value is None the argument is omitted from the query, and hence the database default used.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(PgFunc::websearch_to_tsquery("a b", None))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT WEBSEARCH_TO_TSQUERY('a b')"#
);

pub fn ts_rank<T>(vector: T, query: T) -> FunctionCall

where T: Into<Expr>,

Call TS_RANK function. Postgres only.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(PgFunc::ts_rank("a b", "a&b"))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT TS_RANK('a b', 'a&b')"#
);

pub fn ts_rank_cd<T>(vector: T, query: T) -> FunctionCall

where T: Into<Expr>,

Call TS_RANK_CD function. Postgres only.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(PgFunc::ts_rank_cd("a b", "a&b"))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT TS_RANK_CD('a b', 'a&b')"#
);

pub fn any<T>(expr: T) -> FunctionCall

where T: Into<Expr>,

Call ANY function. Postgres only.

Examples

#[cfg(feature = "postgres-array")]
use sea_query::{tests_cfg::*, *};

let query = Query::select().expr(PgFunc::any(vec![0, 1])).to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT ANY(ARRAY [0,1])"#
);

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .column("id")
    .from("post")
    .and_where(Expr::col("tag").eq(PgFunc::any(Expr::cust("string_to_array('a,b,c', ',')"))))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "id" FROM "post" WHERE "tag" = ANY(string_to_array('a,b,c', ','))"#
);

pub fn some<T>(expr: T) -> FunctionCall

where T: Into<Expr>,

Call SOME function. Postgres only.

Examples

#[cfg(feature = "postgres-array")]
use sea_query::{tests_cfg::*, *};

let query = Query::select().expr(PgFunc::some(vec![0, 1])).to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT SOME(ARRAY [0,1])"#
);

pub fn all<T>(expr: T) -> FunctionCall

where T: Into<Expr>,

Call ALL function. Postgres only.

Examples

#[cfg(feature = "postgres-array")]
use sea_query::{tests_cfg::*, *};

let query = Query::select().expr(PgFunc::all(vec![0, 1])).to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT ALL(ARRAY [0,1])"#
);

pub fn starts_with<T, P>(text: T, prefix: P) -> FunctionCall

where T: Into<Expr>, P: Into<Expr>,

Call STARTS_WITH function. Postgres only.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(PgFunc::starts_with("123", "1"))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT STARTS_WITH('123', '1')"#
);

pub fn gen_random_uuid() -> FunctionCall

Call GEN_RANDOM_UUID function. Postgres only.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select().expr(PgFunc::gen_random_uuid()).to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT GEN_RANDOM_UUID()"#
);

pub fn json_build_object<T>(pairs: Vec<(T, T)>) -> FunctionCall

where T: Into<Expr>,

Call the JSON_BUILD_OBJECT function. Postgres only.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(PgFunc::json_build_object(vec![
        (Expr::val("a"), Expr::val(1)),
        (Expr::val("b"), Expr::val("2")),
    ]))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT JSON_BUILD_OBJECT('a', 1, 'b', '2')"#
);

pub fn date_trunc<T>(unit: PgDateTruncUnit, expr: T) -> FunctionCall

where T: Into<Expr>,

Call the DATE_TRUNC function. Postgres only.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(PgFunc::date_trunc(
        PgDateTruncUnit::Day,
        Expr::val("2020-01-01"),
    ))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT DATE_TRUNC('day', '2020-01-01')"#
);

let query = Query::select()
    .expr(PgFunc::date_trunc(
        PgDateTruncUnit::Microseconds,
        Expr::val("2020-01-01"),
    ))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT DATE_TRUNC('microseconds', '2020-01-01')"#
);

pub fn json_agg<T>(expr: T) -> FunctionCall

where T: Into<Expr>,

Call the JSON_AGG function. Postgres only.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .expr(PgFunc::json_agg(Expr::col(Char::SizeW)))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT JSON_AGG("size_w") FROM "character""#
);

pub fn array_agg<T>(expr: T) -> FunctionCall

where T: Into<Expr>,

Call the ARRAY_AGG function. Postgres only.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .expr(PgFunc::array_agg(Expr::col(Char::Id)))
    .group_by_col(Char::Character)
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT ARRAY_AGG("id") FROM "character" GROUP BY "character""#
);

pub fn array_agg_distinct<T>(expr: T) -> FunctionCall

where T: Into<Expr>,

Call the ARRAY_AGG function with the DISTINCT modifier. Postgres only.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .expr(PgFunc::array_agg_distinct(Expr::col(Char::Id)))
    .group_by_col(Char::Character)
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT ARRAY_AGG(DISTINCT "id") FROM "character" GROUP BY "character""#
);

Trait Implementations

impl Clone for PgFunc

fn clone(&self) -> PgFunc

Returns a duplicate of the value.

fn clone_from(&mut self, source: &Self)

Performs copy-assignment from source.

impl Debug for PgFunc

fn fmt(&self, f: &mut Formatter<'_>) -> Result

Formats the value using the given formatter.

impl PartialEq for PgFunc

fn eq(&self, other: &PgFunc) -> bool

Tests for self and other values to be equal, and is used by ==.

fn ne(&self, other: &Rhs) -> bool

Tests for !=. The default implementation is almost always sufficient, and should not be overridden without very good reason.

impl StructuralPartialEq for PgFunc