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
::jsonbcast:{ '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"}'::jsonbplain string — treated as a pre-encoded JSON string, bound as-is:
{ 'me.data' => { '@>' => '{"status":"active"}' } } # WHERE "me"."data" @> '{"status":"active"}'::jsonbscalar 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 shapekey => $constraintresolves to. The columns are rendered:ON CONFLICT ("a", "b"). A DBIO constraint name is client-side metadata fromadd_unique_constraintand 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 withaction => 'nothing'; withaction => 'update'it throws, because PostgreSQL requires the arbiter index to be named forDO UPDATE.action => 'nothing'—DO NOTHINGaction => 'update'—DO UPDATE SET ...fromsetwhere => $cond—DO UPDATE SET ... WHERE $cond, rendered by "_upsert_condition_sql" after theSETlist and beforeRETURNING. 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 asEXCLUDED."$column"a literal (
\'count + 1'or\['count + ?', 1]) — inlined verbatim, its bind values passed throughanything else — a placeholder plus a bind value, in the shape of the maker's
bindtype:[ $column => $value ]undercolumns(the sync driver), the bare value undernormal(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 UPDATEbranch 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 asEXCLUDED."$column"— the same marker thesetlist 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
DBIO::PostgreSQL::Storage — PostgreSQL storage (uses this SQL maker)
DBIO::PostgreSQL::JSONB — path expression DSL for JSONB columns
DBIO::SQLMaker — base SQL maker class
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.