NAME
DBIO::PostgreSQL::Result - PostgreSQL-specific Result component for DBIO
VERSION
version 0.900007
DESCRIPTION
DBIO::PostgreSQL::Result is a DBIO Result component that adds PostgreSQL-native metadata to a result class: the PostgreSQL schema (namespace) the table belongs to, custom indexes, triggers, and Row Level Security (RLS) configuration.
Load it with:
package MyApp::DB::Result::User;
use base 'DBIO::Core';
__PACKAGE__->load_components('PostgreSQL::Result');
__PACKAGE__->pg_schema('auth');
__PACKAGE__->table('users');
The schema name from pg_schema is used by DBIO::PostgreSQL::DDL to qualify table names (e.g. auth.users) in generated DDL.
METHODS
pg_schema
__PACKAGE__->pg_schema('auth');
my $name = $class->pg_schema;
Get or set the PostgreSQL schema (namespace) for this result class. When set, DBIO::PostgreSQL::DDL qualifies the table name as schema.table in generated DDL. The name is taken exactly -- pg_schema('AppData') is the schema AppData, not appdata -- see "NAMES AND VALUES" in DBIO::PostgreSQL::DDL.
pg_qualified_table
my $fqn = $class->pg_qualified_table; # e.g. 'auth.users'
Returns the fully-qualified table name schema.table, or just table if no PostgreSQL schema has been set. The result is an SQL name: each part is written by the server's own rule ("quote_ident" in DBIO::PostgreSQL::Util), bare where it is plain lower case and no key word, in double quotes otherwise -- auth.users, auth."UserAccount", "Auth".users -- so it can stand in a statement as it is.
pg_index
__PACKAGE__->pg_index('idx_users_tags' => {
using => 'gin',
columns => ['tags'],
});
__PACKAGE__->pg_index('idx_users_active' => {
columns => ['role'],
where => "role != 'suspended'",
});
__PACKAGE__->pg_index('idx_users_embedding' => {
using => 'ivfflat',
columns => ['embedding'],
opclasses => { embedding => 'vector_l2_ops' },
with => { lists => 100 },
});
__PACKAGE__->pg_index('idx_users_embedding_cos' => {
using => 'hnsw',
columns => ['embedding'],
opclasses => { embedding => 'vector_cosine_ops' },
with => { m => 16, ef_construction => 64 },
});
my $def = $class->pg_index('idx_users_tags');
Get or set the definition for a named PostgreSQL index. The definition hashref accepts:
columns- ArrayRef of column namesusing- index access method (btree,gin,gist,brin,hash,hnsw,ivfflat, etc.), SQL written as givenopclasses- HashRef of column name to operator class, emitted after the column name in the key list (USING hnsw (embedding vector_cosine_ops)). The operator class is SQL and is written as given, with whatever follows it there (gist_trgm_ops(siglen=32),text_pattern_ops DESC NULLS LAST). Only name the columns that need a non-default operator class; the rest are emitted bare and PostgreSQL picks the access method's default. pgvector'shnswhas no default operator class forvector, so anhnswindex must name one (vector_l2_ops,vector_cosine_ops,vector_ip_ops, or thehalfvec_*/sparsevec_*/bit_*variants) orCREATE INDEXis rejected.ivfflatdefaults tovector_l2_opson avectorcolumn, so there only another class has to be named. Namingvector_l2_opsonivfflatis accepted, but as the default it is not printed bypg_get_indexdef, so an introspected index reports no operator class for it. For anexpressionindex the operator class goes inside the expression string instead.where- partial index predicate (SQL expression string)expression- expression index expression (replacescolumns)include- ArrayRef of non-key payload columns for a covering index, emitted asINCLUDE (nickname)after the key list. The columns are stored in the index but not part of its key, so an index-only scan can answer a query without touching the table. Supported bybtree(and, from PostgreSQL 11 on,gist); other access methods reject it.with- storage parameter hashref (e.g.{ lists => 100 }for ivfflat); keys and values are SQL, written as given (key = value)unique- boolean, emitsCREATE UNIQUE INDEX
pg_indexes
my $all = $class->pg_indexes; # hashref of name => def
Returns a copy of all index definitions registered on this result class.
pg_trigger
__PACKAGE__->pg_trigger('users_modified_at' => {
when => 'BEFORE',
event => 'UPDATE',
execute => 'auth.update_modified_at()',
});
my $def = $class->pg_trigger('users_modified_at');
Get or set the definition for a named PostgreSQL trigger. The definition hashref accepts when (BEFORE/AFTER/INSTEAD OF), event (INSERT/UPDATE/DELETE/TRUNCATE), execute (the function to call), and for_each (ROW, the default, or STATEMENT).
pg_triggers
my $all = $class->pg_triggers; # hashref of name => def
Returns a copy of all trigger definitions registered on this result class.
pg_rls
__PACKAGE__->pg_rls({
enable => 1,
force => 1,
policies => {
users_own_data => {
for => 'ALL',
using => 'id = current_setting($$app.current_user_id$$)::uuid',
},
},
});
my $rls = $class->pg_rls;
A policy that reads a session setting, as app.current_user_id above, is fed by the application with set_config('app.current_user_id', ?, false) on the connection it is about to use. On the future_io transport the session of a borrow is its own: when a borrow ends, the pool connection is reset (DISCARD ALL, then the connect actions again; see "The session of a borrow" in DBIO::PostgreSQL::Storage::Async), so a setting of one borrow is empty in the next one. Set it in the transaction that runs the statements it governs, with set_config(..., true) or SET LOCAL. Session advisory locks are released by the reset as well.
Get or set the Row Level Security configuration for this table. The hashref accepts:
enable- boolean, generatesENABLE ROW LEVEL SECURITYforce- boolean, generatesFORCE ROW LEVEL SECURITYpolicies- hashref of policy name to policy definition (for,roles,using,with_check,restrictive).restrictivetrue writesCREATE POLICY ... AS RESTRICTIVE, which narrows what the permissive policies let through; absent or false the policy is permissive, as before. The diff reads the kind off the database (permissiveof DBIO::PostgreSQL::Introspect::Policies) and writes it when it creates a policy; it does not yet notice a policy whose kind changed under an unchanged name.rolesis an ArrayRef of role names for the policy'sTOlist; each is taken exactly, like every other name (aAppUserrole is"AppUser").PUBLIC,CURRENT_USER,CURRENT_ROLEandSESSION_USERare written as the key words they are.usingandwith_checkare SQL expressions and are written as given.
pg_check_constraint
__PACKAGE__->pg_check_constraint('run_status_invariant' =>
"(status IN ('Pending','Running','Done') AND error IS NULL)"
. " OR status IN ('Blocked','Failed','Cancelled')"
);
__PACKAGE__->pg_check_constraint('idx_name' => {
constraint_name => 'idx_name',
definition => 'CHECK (col > 0)',
columns => ['col'],
});
my $def = $class->pg_check_constraint('run_status_invariant');
Get or set a named CHECK constraint. Accepts either a plain string (the CHECK predicate expression) or a hashref with constraint_name, definition (which may already include CHECK), and optional columns.
pg_check_constraints
my $all = $class->pg_check_constraints;
Returns a copy of all CHECK constraint definitions registered on this result class.
pg_extra_ddl
__PACKAGE__->pg_extra_ddl(
'ALTER TABLE my_site ADD CONSTRAINT my_site_paypal_account_id_fkey'
. ' FOREIGN KEY (paypal_account_id) REFERENCES my_paypal_account (id)'
. ' ON DELETE RESTRICT',
);
Appends raw SQL statements to be emitted after the table, indexes, triggers, and RLS DDL. Used for cross-table FKs that DBIO::PostgreSQL::DDL does not auto-generate from belongs_to (see "pg_fk_constraints_from_rels", on by default, for the setting that does). Each call appends; arrayrefs are flattened.
pg_unique_options
__PACKAGE__->add_unique_constraint(users_email_key => ['email']);
__PACKAGE__->pg_unique_options(users_email_key => {
deferrable => 1,
initially_deferred => 1,
});
my $options = $class->pg_unique_options; # { name => { ... } }
Options of a UNIQUE constraint of this class, by the name add_unique_constraint gave it: deferrable and initially_deferred, written by the install as DEFERRABLE INITIALLY IMMEDIATE or DEFERRABLE INITIALLY DEFERRED behind the column list, and by the upgrade. Without options a constraint is not deferrable. initially_deferred without deferrable, another option, and the options of a name that is no UNIQUE constraint of the class are refused when the install script is written (DBIO::PostgreSQL::DDL). A call replaces the options of that name; the accessor returns a copy of all of them.
pg_fk_constraints_from_rels
__PACKAGE__->pg_fk_constraints_from_rels(0);
my $bool = $class->pg_fk_constraints_from_rels;
Get or set whether DBIO::PostgreSQL::DDL should auto-generate FOREIGN KEY constraints from this class's belongs_to relationships. Defaults to true: a result class that sets nothing gets its foreign keys, and switches them off for itself with pg_fk_constraints_from_rels(0). A single relationship is left out with is_foreign_key_constraint => 0 in its attributes (core sets it to 1 on every belongs_to that does not say otherwise), so that no key is written for it, and a belongs_to to a class that is not loaded or a table outside the deploy that declares it no longer refuses the install. "pg_extra_ddl" remains the always-available manual alternative (and the only option for constraints belongs_to can't express).
When true (the default), every belongs_to on this class whose join condition reduces to plain column equality (one or more foreign.col => self.col pairs and nothing else, which is what an unadorned __PACKAGE__->belongs_to(rel => 'Class', 'fk_col') declaration produces) gets a named ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY statement, emitted by install_ddl only after every table in the deploy has been created. The on_delete and on_update attributes of the belongs_to are written as declared, ON DELETE CASCADE and the like. Two relationships over the same columns to the same table with the same actions are one constraint and are written once; with other actions, or to another table, they are refused, as the constraint is named after its table and columns. An action other than NO ACTION, RESTRICT, CASCADE, SET NULL or SET DEFAULT (in any case) is refused, naming the relationship.
The upgrade creates the same constraint, under the same name, on a table it creates or one that lacks it, drops and creates it again when its columns, target or actions change, and drops a foreign key the schema does not declare (DBIO::PostgreSQL::Diff::ForeignKey).
The attributes deferrable and initially_deferred of the belongs_to make the key DEFERRABLE INITIALLY IMMEDIATE or DEFERRABLE INITIALLY DEFERRED, written behind the actions, by the install and by the upgrade. Neither is the default: a relationship that names neither gets a key that is not deferrable. initially_deferred without deferrable is refused, as PostgreSQL refuses a constraint that is INITIALLY DEFERRED and NOT DEFERRABLE. A key that changes its timing is dropped and created again.
A belongs_to with a complex condition (a coderef, or a hash with anything other than a plain column on each side) is skipped -- with a SQL comment recording which relationship and why, never silently -- since its join can't be expressed as a plain-column FOREIGN KEY.
A belongs_to that targets a class that is not loaded, or a table that is not part of the schema deploy, is refused: the script is not written, and the exception, refusing to write the install DDL: ..., names the table, the relationship and the class. The class asked for a foreign key to a table that will not be there; a comment would leave the database without it. Switch it off (0) for a class whose relationships are not meant to become constraints, or whose target is outside the deploy.
The comment of a skipped condition reads -- skipped FK for rel NAME: REASON and is a piece of the script of its own, closed by a semicolon on the next line: skipping one foreign key leaves out that foreign key and nothing behind it. A deploy does not show the comment -- it runs the statements and passes over the notes -- so a skipped relationship is seen in the script ("pg_install_ddl" in DBIO::PostgreSQL), not in the outcome of an install.
register_column
Hooks into the column registration chain. When a column declares a pgvector data type (vector, halfvec or sparsevec, with or without a dimension), the result source's resultset_class is promoted to DBIO::PostgreSQL::ResultSet, so its resultsets carry "nearest_neighbors" in DBIO::PostgreSQL::ResultSet. Declaring the column is the whole opt-in; no other class needs to be named.
The promotion is guarded: a resultset_class that already inherits from DBIO::PostgreSQL::ResultSet is left alone, so a custom resultset that subclasses it keeps its own methods. A custom resultset_class that does not inherit from it is replaced -- subclass DBIO::PostgreSQL::ResultSet if you need both.
A bit column queried with Hamming/Jaccard distance is not auto-detected (bit is a general PostgreSQL type, not a pgvector one); set resultset_class by hand for those.
SEE ALSO
DBIO::PostgreSQL - the schema component (Database layer)
DBIO::PostgreSQL::PgSchema - the PgSchema layer for enums, types, functions
DBIO::PostgreSQL::DDL - generates DDL from result class metadata
DBIO::PostgreSQL::ResultSet - the resultset class a pgvector column promotes to
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.