Enum PgFunc

Source

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

Available on crate feature backend-postgres only.

Expand description

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)§

This enum is marked as 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.

§

Any

Available on crate feature postgres-array only.

§

Some

Available on crate feature postgres-array only.

§

All

Available on crate feature postgres-array only.

Implementations§

Source §

impl PgFunc

Source

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)"#
);

Source §

impl PgFunc

Source

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)"#
);

Source §

impl PgFunc

Source

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""#
);

Source §

impl PgFunc

Source

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)"#
);

Source §

impl PgFunc

Source

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')"#
);

Source

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')"#
);

Source

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')"#
);

Source

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')"#
);

Source

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')"#
);

Source

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')"#
);

Source

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')"#
);

Source

pub fn any<T>(expr: T) -> FunctionCall
where T: Into<Expr>,

Available on crate feature postgres-array only.

Call ANY function. Postgres only.

§Examples

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])"#
);

Source

pub fn some<T>(expr: T) -> FunctionCall
where T: Into<Expr>,

Available on crate feature postgres-array only.

Call SOME function. Postgres only.

§Examples

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])"#
);

Source

pub fn all<T>(expr: T) -> FunctionCall
where T: Into<Expr>,

Available on crate feature postgres-array only.

Call ALL function. Postgres only.

§Examples

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])"#
);

Source

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')"#
);

Source

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()"#
);

Source

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')"#
);

Source

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')"#
);

Source

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""#
);

Source

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""#
);

Source

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""#
);