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,
}

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.

§

All

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 §