MigsLab PostgreSQL
Retrieve
6 samples, from the least code that works to complete workflows.
What a read does
| Retrieve | One record by id (RetrieveAsync("account", id, columns)), or by a key or columns (RetrieveAsync(entity, new ArxRetrieveOptions { Match = ArxMatch.Key("email") })). Null when nothing matches; ARX2003 when a key or columns match more than one row |
| What it reads from | A table, a view, a materialized view, a partitioned or inherited table, a foreign table, or a function that returns rows (ArxRowSource.Function("fn", ("p_arg", value))). Procedures and cursors are Execute's. The samples name views vw_, materialized views mv_, functions fn_ and procedures sp_ |
| Columns | The ones asked for, plus the declared id (so RecordId is set); none means all. column._lk.table.c1,c2 joins the referenced table directly (columns column.c1); a link entity's columns come back as alias.column |
| Values read back | date → DateOnly, time → TimeOnly, timestamptz → UTC DateTime, jsonb → ArxJsonB, enums → their label, arrays → T[] (T?[] with NULLs); NULL is a null attribute, "" stays "" |
| Lookups | A column declared with .Lookup(...) reads back as an ArxEntityReference, its Name filled from the referenced table's .DisplayName(...) |
| Functions | On a connection ARX opens itself, read in a read-only transaction: a function that writes is refused (ARX6001) unless the source says .AllowWrites(). Inside an ArxTransaction or a batch scope, or on a connection you pass in, the read runs in that transaction as it is, so a function that writes does write: ARX can't make your transaction read-only. Grant the database user only what the function needs |
| Security | A name carrying SQL (a sort field, a filter field, a table from a request) is ARX1017, refused before the database; a name that breaks the name rules (a space, a hyphen) is ARX1003, and a well-formed name the table doesn't have (a misspelling) comes back from the database as ARX5002 |
| Without a catalog | By id or key, the table is read from the database first to find the row (one more round trip per call, so much slower than with a catalog: meant for simple CRUD and small applications). The values come back as they would with a catalog, so without one a read sets no RecordId (ask for the id column) and a lookup column is its plain value (Basic4) |
Reference
Value types read back
| PostgreSQL column | Reads back as | Notes |
|---|---|---|
text, varchar(n), char(n) |
string |
char(n) keeps its padding spaces; "" stays "" |
smallint / integer / bigint |
short / int / long |
|
numeric(p, s) |
decimal |
More than 28 significant digits: ARX4001 ValueOutOfRange. NaN, Infinity and -Infinity, which a numeric can hold and a decimal can't, read back as a double (from 0.1.0-preview.5; the read failed before) |
real / double precision |
float / double |
|
boolean |
bool |
|
uuid |
Guid |
|
timestamptz |
DateTime, Kind Utc |
The instant, in UTC whatever the session's time zone |
timestamp |
DateTime, Kind Unspecified |
The wall-clock time as stored |
date / time / interval |
DateOnly / TimeOnly / TimeSpan |
An interval with months or years: ARX4004 DataTypeMismatch (a TimeSpan can't hold one) |
bytea |
byte[] |
|
json, jsonb |
ArxJsonB |
SerializedValue holds the JSON text |
An array (text[], int[], date[], …) |
T[] (string[], int[], DateOnly[], …) |
With NULL elements, a value type reads as T?[] (int?[]) |
| An enum type | string |
Its label, from the first read when the schema is preloaded |
| A composite type | string |
Its text form |
| A domain | Its base type | |
A declared lookup (.Lookup(...)) |
ArxEntityReference |
Name from the referenced table's .DisplayName(...) |
| A selection set column | Its stored code | entity.GetSelectionSet(column, set, catalog) gives the option |
| NULL | null |
The attribute is present, with a null value |
RealCase1_EveryDataTypeReadBack writes one row of every type with Create and prints each value's .NET type as it
reads back.
Aggregates
| Aggregate | On PostgreSQL | Reads back as |
|---|---|---|
| Count, CountBig, CountDistinct, CountBigDistinct | count(*), or of the attribute when one is named |
long |
| Sum | of smallint or integer, a bigint; of bigint or numeric, a numeric; of floating point, a double precision |
long, decimal or double |
| Avg | of integers or numeric, a numeric (2 and 3 give 2.5); of floating point, a double precision |
decimal or double |
| Min, Max | as they are | the column's type |
| StDev, StDevP, Var, VarP | stddev_samp, stddev_pop, var_samp, var_pop, in the same types as Avg |
decimal or double |
Error codes a read can raise
| Code | When |
|---|---|
| ARX1001 InvalidArgument | An id of the wrong type or one that can't exist; a NoIdColumn table read by id; an operator without the values it needs (Between with one value, Equal with NULL, NULL in an In list); paging values below 1; Top with PageInfo; an alias used twice or reserved; a link asking for all columns |
| ARX1002, ARX1003, ARX1004 | A missing table name; an invalid name; a column asked for twice |
| ARX1016 IdNotDeclared | Retrieve by id or key on an undeclared table; a _lk join on an undeclared table without ~column |
| ARX1017 SuspectedInjection | A table, column, alias, condition, order, group-by, aggregate, function or argument name carrying SQL |
| ARX2003 MultipleRecordsMatched | Retrieve by a key or columns found more than one row |
| ARX4001 ValueOutOfRange | A numeric larger than a .NET decimal can hold |
| ARX4004 DataTypeMismatch | A value of the wrong type for its column; Contains on a number; an interval with months; no function with those argument types |
| ARX5001, ARX5002 | A table or column that doesn't exist; a selected column that isn't grouped |
| ARX6001 PermissionDenied | No SELECT permission; a function that writes, read without AllowWrites() on a connection ARX opens |
| ARX7003 Timeout | CommandTimeout exceeded |
| ARX8001 NotSupported | A removed column form (._opt, ignoredefault.); a materialized view never refreshed; a SETOF record function without declared columns; a procedure |
The samples
Basics
The least code that works.
1Retrieve one recordStandaloneThe smallest read: one record, in three scenarios. The usual way first (the id CreateAsync returned, and the columns you want), then a text id, then an id nobody has: Retrieve returns null, not an error.
Which columns come back, in three scenarios: the ones you ask for (plus the id); all of them; and how an empty value differs from a NULL one.
The three ways to find one record, in four scenarios: by id, by a declared key, by any columns, and what happens when columns match more than one row.
One record read on a service created with only a connection string, in two scenarios: by the id, which ARX finds from the table's primary key; and by a key, the unique account number.
Real cases
What an application does: a small CRM and product catalogue.
1Every data type, written and read backProgressiveWhat each PostgreSQL type reads back as, in one scenario: a row of data_type_showcase is written with Create and read with Retrieve, and each attribute's .NET type is printed.
Advanced cases
Dependency injection, transactions, batch scopes and setting the service up.
1Read your own writes in an ARX transactionAdvancedReads inside an ARX transaction, in two scenarios: a read sees the transaction's own uncommitted writes, another connection doesn't; and a check that reads before it commits, rolling back when the check fails.