NAME

DBIO::PostgreSQL::SQLMaker - PostgreSQL-specific SQL generation for DBIO

VERSION

version 0.900007

DESCRIPTION

DBIO::SQLMaker subclass for PostgreSQL. Extends standard SQL generation with native support for PostgreSQL JSONB operators.

Used automatically by DBIO::PostgreSQL::Storage — the sql_maker_class is set on the storage and this class is instantiated transparently whenever a PostgreSQL connection is opened.

Containment: @> and <@

Tests whether a JSONB value contains (or is contained by) another JSONB value. The RHS hashref or arrayref is JSON-encoded automatically.

$rs->search({ 'me.data' => { '@>' => { status => 'active' } } });
# WHERE "me"."data" @> '{"status":"active"}'::jsonb

$rs->search({ 'me.tags' => { '<@' => ['a', 'b'] } });
# WHERE "me"."tags" <@ '["a","b"]'::jsonb

Key existence: ?, ?|, ?&

Tests whether a JSONB object has a specific key (or any/all keys from a list). These operators are rewritten as jsonb_exists*() functions to avoid conflicts with DBI's ? placeholder syntax.

$rs->search({ 'me.data' => { '?'  => 'email' } });
# WHERE jsonb_exists("me"."data", ?)

$rs->search({ 'me.data' => { '?|' => [qw(email phone)] } });
# WHERE jsonb_exists_any("me"."data", ARRAY[?, ?])

$rs->search({ 'me.data' => { '?&' => [qw(name email)] } });
# WHERE jsonb_exists_all("me"."data", ARRAY[?, ?])

JSONPath: @? and @@ (PostgreSQL 12+)

Evaluates a JSONPath expression against a JSONB value.

$rs->search({ 'me.data' => { '@?' => '$.status == "active"' } });
# WHERE "me"."data" @\? ?::jsonpath

$rs->search({ 'me.data' => { '@@' => '$.score > 10' } });
# WHERE "me"."data" @@ ?::jsonpath

The jsonpath is a bind value. The ? of the operator @? is written \?, a question mark that is no placeholder: as_query shows @\?, the server gets @?. Literal SQL that uses the operator writes it the same way wherever the statement is prepared; $dbh->do without bind values sends a statement as it is, backslash included.

Path extraction

For comparing individual fields within a JSONB value, see DBIO::PostgreSQL::JSONB which provides the jsonb() path expression helper:

use DBIO::PostgreSQL::JSONB qw(jsonb);
$rs->search( jsonb('me.data', 'status')->eq('active') );
# WHERE (me.data->>?) = ?

METHODS

new

Extends the base constructor to register the JSONB operators (@>, <@, @?, @@, ?, ?|, ?&) as expand_op handlers. Called automatically by DBIO::PostgreSQL::Storage — no need to instantiate this class directly.

_where_op_jsonb_contains

Handles the @> (contains) and <@ (contained by) operators.

The RHS may be:

  • hashref or arrayref — JSON-encoded automatically and bound with a ::jsonb cast:

    { 'me.data' => { '@>' => { status => 'active' } } }
    # WHERE "me"."data" @> '{"status":"active"}'::jsonb
    
    { 'me.tags' => { '@>' => ['admin', 'user'] } }
    # WHERE "me"."tags" @> '["admin","user"]'::jsonb
    
    { 'me.data' => { '<@' => { role => 'guest' } } }
    # WHERE "me"."data" <@ '{"role":"guest"}'::jsonb
  • plain string — treated as a pre-encoded JSON string, bound as-is:

    { 'me.data' => { '@>' => '{"status":"active"}' } }
    # WHERE "me"."data" @> '{"status":"active"}'::jsonb
  • scalar ref — embedded as literal SQL without binding (use for sub-selects or other column references):

    { 'me.data' => { '@>' => \'other_col' } }
    # WHERE "me"."data" @> other_col

_where_op_jsonb_path

Handles @? (jsonpath predicate) and @@ (jsonpath match) operators (PostgreSQL 12+). The RHS is bound with a ::jsonpath cast.

{ 'me.data' => { '@?' => '$.status == "active"' } }
# WHERE "me"."data" @\? ?::jsonpath

{ 'me.data' => { '@@' => '$.score > 10' } }
# WHERE "me"."data" @@ ?::jsonpath

The operator @? is written @\?, and that is what as_query shows. A ? in a statement is a placeholder; \? is a question mark that is none. DBD::Pg reads it so and sends @? to the server, and a transport that numbers the placeholders itself reads it the same way ("number_placeholders" in DBIO::PostgreSQL::Util). The statement has one placeholder for its one bind on every way it is sent.

_where_op_jsonb_exists

Handles the ? (key exists), ?| (any key exists), and ?& (all keys exist) operators. They are written as the operators, not as the functions behind them (jsonb_exists(), jsonb_exists_any(), jsonb_exists_all()): the planner answers an operator from a GIN index over the column and does not see through a function call. The ? of the operator is written \?, as that of @? is and for the same reason (see "_where_op_jsonb_path").

{ 'me.data' => { '?'  => 'email' } }
# WHERE "me"."data" \? ?

{ 'me.data' => { '?|' => [qw(email phone)] } }
# WHERE "me"."data" \?| ARRAY[?, ?]

{ 'me.data' => { '?&' => [qw(name email)] } }
# WHERE "me"."data" \?& ARRAY[?, ?]

A single string is accepted for ?| and ?& as a convenience (treated as a one-element list):

{ 'me.data' => { '?|' => 'email' } }
# WHERE "me"."data" \?| ARRAY[?]

insert

Extends the generic insert with the atomic-upsert conflict clause (ADR 0036). When $options->{on_conflict} is present it is consumed here and rendered by "_insert_on_conflict"; the clause is placed between the VALUES list and the RETURNING tail, which is where PostgreSQL's grammar wants it. A conditional DO UPDATE puts its WHERE inside that clause, after the SET list and still before RETURNING.

A plain insert (no on_conflict key) is passed straight through untouched.

_insert_on_conflict

my ($sql_tail, @bind) = $sqlmaker->_insert_on_conflict(\%on_conflict);

Renders the normalized conflict structure that "upsert" in DBIO::ResultSet builds into a PostgreSQL ON CONFLICT tail. Mirrors _insert_returning: it returns a leading-space SQL fragment plus its bind values, and appends nothing to the statement itself.

The structure is { target => ..., action => ..., set => ..., where => ... }, where where is present only when the caller passed update_where:

  • target => \@columns — ON CONFLICT ("a", "b")

  • target => { constraint => $name, columns => \@columns } — the shape key => $constraint resolves to. The columns are rendered: ON CONFLICT ("a", "b"). A DBIO constraint name is client-side metadata from add_unique_constraint and need not name anything the server knows — 'primary' never does — while the resolved columns name the same index and are correct in every case (ADR 0036 section 2).

  • target => { constraint => $name } — a name and no columns, which nothing here can resolve, is taken at face value as a server-side constraint: ON CONFLICT ON CONSTRAINT "name". Core no longer sends this shape; it is the explicit opt-in for a caller who really means a named constraint (and what a core that still sends the constraint name passes).

  • target => 'any' — no target. Valid only with action => 'nothing'; with action => 'update' it throws, because PostgreSQL requires the arbiter index to be named for DO UPDATE.

  • action => 'nothing' — DO NOTHING

  • action => 'update' — DO UPDATE SET ... from set

  • where => $cond — DO UPDATE SET ... WHERE $cond, rendered by "_upsert_condition_sql" after the SET list and before RETURNING. Absent unless the caller asked for a condition.

Every route to a dropped condition throws instead of rendering, because the statement left behind is a perfectly valid unconditional DO UPDATE — the overwrite update_where exists to prevent, and the one failure mode with no symptom of its own. Those routes are a where on the action => 'nothing' branch (there is no update branch for it to condition), an undef one (core sets the key only when it has a condition, so the key alone is enough to mean one was asked for), and one that renders to an empty string ({ -and => [] }, which core does not walk).

Each value in set is one of:

  • { -attempted => $column } — the value the statement tried to insert, rendered as EXCLUDED."$column"

  • a literal (\'count + 1' or \['count + ?', 1]) — inlined verbatim, its bind values passed through

  • anything else — a placeholder plus a bind value, in the shape of the maker's bindtype: [ $column => $value ] under columns (the sync driver), the bare value under normal (the future_io and ev transports)

_upsert_attempted_sql

my $sql = $sqlmaker->_upsert_attempted_sql('updated_at');
# EXCLUDED."updated_at"

The engine-specific half of the upsert condition (ADR 0036): renders core's engine-neutral { -attempted => $column } marker as the PostgreSQL EXCLUDED pseudo-row reference. This is the hook the core helper "_upsert_attempted_sql" in DBIO::SQLMaker::ClassicExtensions calls for the token — core has no default for it and throws, because the pseudo-row spelling is engine grammar. One method for both places the marker can appear — the set list and the conditional branch — so the two halves of the same statement can never spell it differently.

_upsert_condition_sql

my ($sql, @bind) = $sqlmaker->_upsert_condition_sql(\%cond, $table);

The where half of the conflict structure — the condition of the DO UPDATE branch (ADR 0036 section 1). This is the core helper "_upsert_condition_sql" in DBIO::SQLMaker::ClassicExtensions, inherited from DBIO::SQLMaker; the substitution mechanics — registering the marker as an SQL::Abstract operator for the duration of the call, and why a -literal is right in every position — live there, not here. The helper returns the bare condition without the WHERE keyword, like _recurse_where does, plus its bind values; "_insert_on_conflict" splices both in after the SET list.

$table is the target table the statement inserts into; "insert" threads it through so the stored-side columns can be qualified with it (see below). It is optional; without it the condition is rendered with its columns unqualified.

Two vocabularies, both engine-neutral:

  • an unqualified column is the stored row — the row the statement collided with. In an ON CONFLICT DO UPDATE branch that row shares the scope with the attempted-values pseudo-row (EXCLUDED), so a bare column is ambiguous to the engine — PostgreSQL rejects it outright ("column reference is ambiguous"). Given $table, every bare (single-part) column is therefore qualified with the target table, at any depth; an already-qualified column (it carries its own table part) and the { -attempted } marker are left exactly as they are.

  • { -attempted => $column } is the value the statement tried to insert, rendered as EXCLUDED."$column" — the same marker the set list uses, but here it may appear at any depth.

# with $table = 'artist':
{ updated_at => { '<' => { -attempted => 'updated_at' } } }
# "artist"."updated_at" < EXCLUDED."updated_at"   -- the last-write-wins guard

A literal (\'excluded.rank > artist.rank' or \[ $sql, @bind ]) passes through untouched, for conditions the abstract form cannot express.

select

Extends the base select only to stash the resolved search attributes so the "_lock_select" override below can see the skip_locked / nowait siblings of the for attribute. The SQL is otherwise generated entirely by the base.

_lock_select

Renders the row-locking tail. The FOR UPDATE / FOR SHARE keyword still comes from the base; this override appends the PostgreSQL wait-policy modifier when the search carries one:

$rs->search({}, { for => 'update', skip_locked => 1 });
# ... FOR UPDATE SKIP LOCKED

$rs->search({}, { for => 'update', nowait => 1 });
# ... FOR UPDATE NOWAIT

$rs->search({}, { for => 'shared', skip_locked => 1 });
# ... FOR SHARE SKIP LOCKED

skip_locked and nowait are mutually exclusive — PostgreSQL rejects both on one lock clause — so setting both throws.

SEE ALSO

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.