Spi
Namespace: Ankus
Assembly: Ankus.Runtime.dll
Executes SQL inside the current PostgreSQL backend through the Server Programming Interface.
public static class SpiInheritance: object
Methods
Section titled “Methods”Connect(Action<SpiSession>)
Section titled “Connect(Action<SpiSession>)”Runs a synchronous callback using one scoped SPI connection. Session-bound plans expire when the callback exits.
public static void Connect(Action<SpiSession> action)Parameters:
action — Action<SpiSession>
The synchronous backend-thread callback.
Connect<TResult>(Func<SpiSession, TResult>)
Section titled “Connect<TResult>(Func<SpiSession, TResult>)”Runs a synchronous callback using one scoped SPI connection and returns its managed result.
public static TResult Connect<TResult>(Func<SpiSession, TResult> action)Type parameters:
TResult
The callback result type.
Parameters:
action — Func<SpiSession, TResult>
The synchronous backend-thread callback. It must not start asynchronous session operations.
Returns: TResult
The callback result, whose owned rows and retained plans can outlive the session.
Execute(SpiCommand)
Section titled “Execute(SpiCommand)”Executes a parameterized command, retaining successful writes and rolling back a failed call.
public static long Execute(SpiCommand command)Parameters:
command — SpiCommand
SQL and its declared bindings.
Returns: long
The final statement’s processed row count.
Execute(string, params ReadOnlySpan<SpiParameter>)
Section titled “Execute(string, params ReadOnlySpan<SpiParameter>)”Executes SQL and returns the rows processed by its final statement. The call uses an internal subtransaction: success retains changes in the enclosing transaction; failure rolls back this call’s changes before throwing a managed PostgreSQL error.
public static long Execute(string commandText, params ReadOnlySpan<SpiParameter> parameters)Parameters:
commandText — string
The SQL commands to execute.
parameters — ReadOnlySpan<SpiParameter>
Typed positional parameters referenced as $1, $2, and so on.
Returns: long
The number of rows processed by the final statement.
Exceptions:
- PgException: PostgreSQL rejected the command.
- InvalidOperationException: The caller is not on the active PostgreSQL backend thread.
ExecuteScalar<T>(SpiCommand)
Section titled “ExecuteScalar<T>(SpiCommand)”Reads the first parameterized result cell without implicit conversion or limiting command effects.
public static T ExecuteScalar<T>(SpiCommand command)Type parameters:
T
The expected managed result type.
Parameters:
command — SpiCommand
SQL and its declared bindings.
Returns: T
The scalar value with the existing SQL NULL and empty-result rules.
ExecuteScalar<T>(string, params ReadOnlySpan<SpiParameter>)
Section titled “ExecuteScalar<T>(string, params ReadOnlySpan<SpiParameter>)”Reads the first column of the first result row without implicit type conversion. SQL NULL and an empty result return null for nullable or reference types and reject non-nullable value types.
public static T ExecuteScalar<T>(string commandText, params ReadOnlySpan<SpiParameter> parameters)Type parameters:
T
The expected managed result type.
Parameters:
commandText — string
The SQL command.
parameters — ReadOnlySpan<SpiParameter>
Typed positional parameters.
Returns: T
The scalar result.
ExecuteScalars<TFirst, TSecond>(SpiCommand)
Section titled “ExecuteScalars<TFirst, TSecond>(SpiCommand)”Reads two parameterized result cells without implicit conversion or limiting command effects.
public static (TFirst First, TSecond Second) ExecuteScalars<TFirst, TSecond>(SpiCommand command)Type parameters:
TFirst
The first column’s managed type.
TSecond
The second column’s managed type.
Parameters:
command — SpiCommand
SQL and its declared bindings.
Returns: (TFirst First, TSecond Second)
The first row’s two values with the existing SQL NULL and empty-result rules.
ExecuteScalars<TFirst, TSecond>(string, params ReadOnlySpan<SpiParameter>)
Section titled “ExecuteScalars<TFirst, TSecond>(string, params ReadOnlySpan<SpiParameter>)”Reads the first two columns of the first row without limiting command execution. SQL NULL or an empty result requires nullable value types or reference types.
public static (TFirst First, TSecond Second) ExecuteScalars<TFirst, TSecond>(string commandText, params ReadOnlySpan<SpiParameter> parameters)Type parameters:
TFirst
The first column’s managed type.
TSecond
The second column’s managed type.
Parameters:
commandText — string
The SQL commands to execute.
parameters — ReadOnlySpan<SpiParameter>
Typed positional parameters.
Returns: (TFirst First, TSecond Second)
The first two values from the final statement, without implicit type conversions.
Exceptions:
- InvalidOperationException: A returned row has fewer than two columns, or SQL NULL is read as a non-nullable value type.
- InvalidCastException: A column cannot be read as its requested managed type.
ExecuteScalars<TFirst, TSecond, TThird>(SpiCommand)
Section titled “ExecuteScalars<TFirst, TSecond, TThird>(SpiCommand)”Reads three parameterized result cells without implicit conversion or limiting command effects.
public static (TFirst First, TSecond Second, TThird Third) ExecuteScalars<TFirst, TSecond, TThird>(SpiCommand command)Type parameters:
TFirst
The first column’s managed type.
TSecond
The second column’s managed type.
TThird
The third column’s managed type.
Parameters:
command — SpiCommand
SQL and its declared bindings.
Returns: (TFirst First, TSecond Second, TThird Third)
The first row’s three values with the existing SQL NULL and empty-result rules.
ExecuteScalars<TFirst, TSecond, TThird>(string, params ReadOnlySpan<SpiParameter>)
Section titled “ExecuteScalars<TFirst, TSecond, TThird>(string, params ReadOnlySpan<SpiParameter>)”Reads the first three columns of the first row without limiting command execution. SQL NULL or an empty result requires nullable value types or reference types.
public static (TFirst First, TSecond Second, TThird Third) ExecuteScalars<TFirst, TSecond, TThird>(string commandText, params ReadOnlySpan<SpiParameter> parameters)Type parameters:
TFirst
The first column’s managed type.
TSecond
The second column’s managed type.
TThird
The third column’s managed type.
Parameters:
commandText — string
The SQL commands to execute.
parameters — ReadOnlySpan<SpiParameter>
Typed positional parameters.
Returns: (TFirst First, TSecond Second, TThird Third)
The first three values from the final statement, without implicit type conversions.
Exceptions:
- InvalidOperationException: A returned row has fewer than three columns, or SQL NULL is read as a non-nullable value type.
- InvalidCastException: A column cannot be read as its requested managed type.
Explain(SpiCommand)
Section titled “Explain(SpiCommand)”Plans a parameterized statement without executing EXPLAIN ANALYZE.
public static PgJson Explain(SpiCommand command)Parameters:
command — SpiCommand
One SQL statement and its declared bindings.
Returns: PgJson
The owned JSON query plan.
Explain(string, params ReadOnlySpan<SpiParameter>)
Section titled “Explain(string, params ReadOnlySpan<SpiParameter>)”Plans one SQL statement and returns PostgreSQL’s JSON EXPLAIN output. The statement is not executed by EXPLAIN ANALYZE.
public static PgJson Explain(string commandText, params ReadOnlySpan<SpiParameter> parameters)Parameters:
commandText — string
The statement to explain.
parameters — ReadOnlySpan<SpiParameter>
The typed positional parameters.
Returns: PgJson
The owned JSON query plan.
FindCursor(string)
Section titled “FindCursor(string)”Takes ownership of an existing PostgreSQL cursor by its exact portal name.
public static SpiCursor FindCursor(string name)Parameters:
name — string
The portal name, including names returned by SpiCursor.Detach.
Returns: SpiCursor
An owned cursor.
OpenCursor(SpiCommand)
Section titled “OpenCursor(SpiCommand)”Opens a parameterized cursor using PostgreSQL’s transaction-aware snapshot policy.
public static SpiCursor OpenCursor(SpiCommand command)Parameters:
command — SpiCommand
One row-producing statement and its declared bindings.
Returns: SpiCursor
An owned transaction-bound cursor.
OpenCursor(SpiCommand, bool)
Section titled “OpenCursor(SpiCommand, bool)”Opens a parameterized cursor with explicit read-only SPI execution mode.
public static SpiCursor OpenCursor(SpiCommand command, bool readOnly)Parameters:
command — SpiCommand
One row-producing statement and its declared bindings.
readOnly — bool
Whether to enforce read-only SPI execution.
Returns: SpiCursor
An owned transaction-bound cursor.
OpenCursor(string, bool, params ReadOnlySpan<SpiParameter>)
Section titled “OpenCursor(string, bool, params ReadOnlySpan<SpiParameter>)”Opens a cursor with explicit read-only SPI execution mode.
public static SpiCursor OpenCursor(string commandText, bool readOnly, params ReadOnlySpan<SpiParameter> parameters)Parameters:
commandText — string
One SQL command that returns rows.
readOnly — bool
Whether PostgreSQL should use read-only execution.
parameters — ReadOnlySpan<SpiParameter>
The positional parameter values.
Returns: SpiCursor
An owned transaction-bound cursor.
OpenCursor(string, params ReadOnlySpan<SpiParameter>)
Section titled “OpenCursor(string, params ReadOnlySpan<SpiParameter>)”Opens a transaction-bound cursor with optional typed positional parameters. Uses a read-only snapshot until the transaction becomes writable, then uses a fresh writable snapshot.
public static SpiCursor OpenCursor(string commandText, params ReadOnlySpan<SpiParameter> parameters)Parameters:
commandText — string
One SQL command that returns rows.
parameters — ReadOnlySpan<SpiParameter>
The positional parameter values.
Returns: SpiCursor
An owned cursor that should be disposed or detached.
Prepare(string, params ReadOnlySpan<Type>)
Section titled “Prepare(string, params ReadOnlySpan<Type>)”Prepares a reusable statement with explicitly declared managed parameter types. The statement survives SPI calls and transaction boundaries until disposed on the owning backend thread.
public static SpiPreparedStatement Prepare(string commandText, params ReadOnlySpan<Type> parameterTypes)Parameters:
commandText — string
The SQL commands to prepare.
parameterTypes — ReadOnlySpan<Type>
The declared CLR types of $1, $2, and subsequent parameters.
Returns: SpiPreparedStatement
A statement that must be disposed from an extension callback on its owning backend.
PrepareWithTypeOids(string, params ReadOnlySpan<uint>)
Section titled “PrepareWithTypeOids(string, params ReadOnlySpan<uint>)”Prepares a reusable statement with explicit PostgreSQL parameter type identities, including named composites and domains.
public static SpiPreparedStatement PrepareWithTypeOids(string commandText, params ReadOnlySpan<uint> parameterTypeOids)Parameters:
commandText — string
The SQL commands to prepare.
parameterTypeOids — ReadOnlySpan<uint>
The nonzero catalog type OIDs for $1, $2, and subsequent parameters.
Returns: SpiPreparedStatement
A statement that must be disposed on its owning backend.
Query(SpiCommand, bool, int)
Section titled “Query(SpiCommand, bool, int)”Executes a parameterized command with explicit snapshot mode and a row limit.
public static SpiResult Query(SpiCommand command, bool readOnly = false, int limit = 0)Parameters:
command — SpiCommand
SQL and its declared bindings.
readOnly — bool
Whether to enforce read-only SPI execution.
limit — int
The maximum returned rows, or zero for unlimited.
Returns: SpiResult
Owned managed rows and column metadata.
Query(string, bool, int, params ReadOnlySpan<SpiParameter>)
Section titled “Query(string, bool, int, params ReadOnlySpan<SpiParameter>)”Executes SQL with explicit snapshot mode and a row limit, copying result data into managed memory.
public static SpiResult Query(string commandText, bool readOnly, int limit, params ReadOnlySpan<SpiParameter> parameters)Parameters:
commandText — string
The SQL command.
readOnly — bool
Whether PostgreSQL should use read-only SPI execution.
limit — int
The maximum returned rows, or zero for no limit.
parameters — ReadOnlySpan<SpiParameter>
Typed positional parameters.
Returns: SpiResult
The materialized result.
Query(string, params ReadOnlySpan<SpiParameter>)
Section titled “Query(string, params ReadOnlySpan<SpiParameter>)”Executes SQL and copies all result rows and metadata into managed objects.
public static SpiResult Query(string commandText, params ReadOnlySpan<SpiParameter> parameters)Parameters:
commandText — string
The SQL command.
parameters — ReadOnlySpan<SpiParameter>
Typed positional parameters.
Returns: SpiResult
The materialized result.
QueryRaw(SpiCommand, bool, int)
Section titled “QueryRaw(SpiCommand, bool, int)”Executes a parameterized command with owned native results and explicit snapshot mode.
public static SpiRawResult QueryRaw(SpiCommand command, bool readOnly = false, int limit = 0)Parameters:
command — SpiCommand
SQL and its declared bindings.
readOnly — bool
Whether to enforce read-only SPI execution.
limit — int
The maximum returned rows, or zero for unlimited.
Returns: SpiRawResult
An owned native result to dispose before leaving the backend callback.
QueryRaw(string, bool, int, params ReadOnlySpan<SpiParameter>)
Section titled “QueryRaw(string, bool, int, params ReadOnlySpan<SpiParameter>)”Copies raw native results with explicit snapshot mode and row limit.
public static SpiRawResult QueryRaw(string commandText, bool readOnly, int limit, params ReadOnlySpan<SpiParameter> parameters)Parameters:
commandText — string
The SQL command.
readOnly — bool
Whether PostgreSQL should use read-only SPI execution.
limit — int
The maximum returned rows, or zero for no limit.
parameters — ReadOnlySpan<SpiParameter>
Typed positional parameters.
Returns: SpiRawResult
An owned result whose datums expire on disposal or callback-context cleanup.
QueryRaw(string, params ReadOnlySpan<SpiParameter>)
Section titled “QueryRaw(string, params ReadOnlySpan<SpiParameter>)”Copies native result values without requiring a managed mapping for their PostgreSQL types.
public static SpiRawResult QueryRaw(string commandText, params ReadOnlySpan<SpiParameter> parameters)Parameters:
commandText — string
The SQL command.
parameters — ReadOnlySpan<SpiParameter>
Typed positional parameters.
Returns: SpiRawResult
A result to dispose before leaving the backend callback.
QuoteIdentifier(string)
Section titled “QuoteIdentifier(string)”Quotes a single SQL identifier using PostgreSQL’s keyword rules and quote_all_identifiers setting.
public static string QuoteIdentifier(string identifier)Parameters:
identifier — string
One identifier; dots are part of its name.
Returns: string
The quoted identifier or the original spelling when quoting is unnecessary.
The quoting helpers need no transaction, so a background worker can also use them between transactions, for example to build replication commands.
QuoteLiteral(string)
Section titled “QuoteLiteral(string)”Quotes a text literal with escaping that is valid regardless of standard_conforming_strings.
public static string QuoteLiteral(string value)Parameters:
value — string
The literal text.
Returns: string
The quoted SQL literal.
QuoteQualifiedIdentifier(string?, string)
Section titled “QuoteQualifiedIdentifier(string?, string)”Quotes a schema or other qualifier and an identifier independently, joining them with a dot.
public static string QuoteQualifiedIdentifier(string? qualifier, string identifier)Parameters:
qualifier — string
The qualifier, or null to omit it.
identifier — string
The identifier.
Returns: string
The qualified SQL name.
Select(SpiCommand, int)
Section titled “Select(SpiCommand, int)”Selects a parameterized command using PostgreSQL’s transaction-aware snapshot policy.
public static SpiResult Select(SpiCommand command, int limit = 0)Parameters:
command — SpiCommand
SQL and its declared bindings.
limit — int
The maximum returned rows, or zero for unlimited.
Returns: SpiResult
Owned managed rows and column metadata.
Select(string, int, params ReadOnlySpan<SpiParameter>)
Section titled “Select(string, int, params ReadOnlySpan<SpiParameter>)”Selects rows using PostgreSQL’s transaction state and an explicit row limit. Writes through other SPI sessions or the caller also select writable snapshots.
public static SpiResult Select(string commandText, int limit, params ReadOnlySpan<SpiParameter> parameters)Parameters:
commandText — string
The SQL query.
limit — int
The maximum returned rows, or zero for no limit.
parameters — ReadOnlySpan<SpiParameter>
Typed positional parameters.
Returns: SpiResult
The materialized rows and metadata.
Select(string, params ReadOnlySpan<SpiParameter>)
Section titled “Select(string, params ReadOnlySpan<SpiParameter>)”Selects rows with a read-only snapshot until the transaction becomes writable, then uses fresh writable snapshots.
public static SpiResult Select(string commandText, params ReadOnlySpan<SpiParameter> parameters)Parameters:
commandText — string
The SQL query.
parameters — ReadOnlySpan<SpiParameter>
Typed positional parameters.
Returns: SpiResult
The materialized rows and metadata.
SelectRaw(SpiCommand, int)
Section titled “SelectRaw(SpiCommand, int)”Selects parameterized native values using PostgreSQL’s transaction-aware snapshot policy.
public static SpiRawResult SelectRaw(SpiCommand command, int limit = 0)Parameters:
command — SpiCommand
SQL and its declared bindings.
limit — int
The maximum returned rows, or zero for unlimited.
Returns: SpiRawResult
An owned native result to dispose before leaving the backend callback.
SelectRaw(string, int, params ReadOnlySpan<SpiParameter>)
Section titled “SelectRaw(string, int, params ReadOnlySpan<SpiParameter>)”Selects owned native values using transaction-aware snapshots and an explicit row limit.
public static SpiRawResult SelectRaw(string commandText, int limit, params ReadOnlySpan<SpiParameter> parameters)Parameters:
commandText — string
The SQL query.
limit — int
The maximum returned rows, or zero for no limit.
parameters — ReadOnlySpan<SpiParameter>
Typed positional parameters.
Returns: SpiRawResult
A result whose datums expire on disposal or callback-context cleanup.
SelectRaw(string, params ReadOnlySpan<SpiParameter>)
Section titled “SelectRaw(string, params ReadOnlySpan<SpiParameter>)”Selects owned native values using PostgreSQL’s transaction state without requiring managed type mappings.
public static SpiRawResult SelectRaw(string commandText, params ReadOnlySpan<SpiParameter> parameters)Parameters:
commandText — string
The SQL query.
parameters — ReadOnlySpan<SpiParameter>
Typed positional parameters.
Returns: SpiRawResult
An owned result to dispose before leaving the backend callback.
Sql(SpiSqlInterpolatedStringHandler)
Section titled “Sql(SpiSqlInterpolatedStringHandler)”Creates SQL with typed positional bindings instead of formatting interpolated values into its text. Values must not be quoted or used as identifiers or SQL fragments in the interpolation.
public static SpiCommand Sql(SpiSqlInterpolatedStringHandler sql)Parameters:
sql — SpiSqlInterpolatedStringHandler
The compiler-built SQL interpolation.
Returns: SpiCommand
Owned SQL and bindings that preserve each value’s declared type and lifetime.